Date and time formats supported in Zoho DataPrep
Datetime
A datetime format is formed using various format strings such as yyyy, dd, mm, etc. All the supported format strings are incorporated into the date picker feature of DataPrep. You can simply click to select the required element to form or customize a date or a datetime format instead of manually writing the format string yourselves. In that sense, the date picker is a collection of format strings of datetime components in the form of tiles to choose from. Date picker is available in the following transforms in Zoho DataPrep.
You can click the calendar icon (which appears while hovering your mouse over the date format input field) in the above transforms to access the date picker and create your own format. Here's a snapshot below.
While choosing the required format from the date picker makes it all the more easier, it could be helpful to understand them as format strings as well.
Time
A time format is formed using various format strings such as HH, hh, mm, ss, etc., and all these format strings are incorporated into the time picker feature. You can simply click the required elements and customize a time format. A time picker is a collection of format strings of time components in the form of tiles to choose from. Time picker is available in the Change time format transform.
Zoho DataPrep supports the datetime and time format strings listed in the table below.
|
Format String
|
Description
|
Example
|
|
yy
|
Indicates Year without century. If the value is from 70 to 99, the year is taken in the 20th century (1970, 1971... 1999).
|
14 (2014)
08 (2008)
|
|
yyyy
|
Indicates Year with century
|
2014
|
|
MM
|
Indicates Month in numeric
|
3 (March)
|
|
MMM
|
Indicates Month as a three letter text abbreviation (Jan, Feb, etc.)
|
Jan
|
| MMMM |
Indicates Month in full text
|
January
|
|
dd
|
Indicates Day in the month
|
12
|
|
EEE
|
Indicates Day in the week (abbreviation)
|
Wed
|
|
EEEE
|
Indicates Day in the week (full form)
|
Wednesday
|
|
HH
|
Indicates Hours in a day between 0 to 23 hrs
|
23:50
|
|
hh
|
Indicates Hours in a day between 1 to 12 hrs (morning or afternoon is identified by AM or PM)
|
10:30
|
|
mm
|
Indicates Minutes in the hour from 01 to 12 |
1:30
|
|
m
|
Indicates Minutes in the hour from 1 to 12
|
1:5
|
|
ss
|
Indicates Seconds in the minute from 00 to 59
|
11:30:45
|
|
s
|
Indicates Seconds in the minute from 0 to 59
|
11:30:5
|
|
SSS
|
Indicates the Millisecond in the Date or Time
|
11:30:45:163
|
|
a
|
Indicates AM/PM of the time
|
11:00 AM, 2:00 PM
|
|
ZZ
|
Indicates the time zone offset with colon separator
|
Nov 24, 2014 10:00 PM +5:30
|
|
Z
|
Indicates the time zone offset without the colon separator
|
Nov 24, 2014 10:00 PM +0530
|
|
millis
|
Indicates number of milliseconds since January 1, 1970. This format is applicable for timestamp data type.
|
1635509077
|
Delimiters
A delimiter is a separator, usually a symbol or a whitespace that separates one date or time element from the other.
For example, in the date: 11/24/2020 , / is the delimiter that separates the date elements. Zoho DataPrep supports the delimiters given in the table below.
|
Category
|
Delimiters
|
|
Date time delimiters
|
/ - . , : _T \s
|
|
AM/PM delimiters
|
\s |
|
Time zone delimiters
|
- Z |
|
Time delimiters
|
/ : - , . \s
|
Note: \s denotes single whitespace. Please provide an actual space when you provide the input.
Supported date formats
Here are a few examples of the supported date formats.
Example 1
Date: 11/24/2020
Format string: MM/dd/yyyy
| 11 | MM | Indicates the month |
| 24 | dd | Indicates the day |
| 2020 | yyyy | Indicates the year |
Example 2
Date: 11.24.2020
Format string: MM.dd.yyyy
| 11 | MM | Indicates the month |
| 24 | dd | Indicates the day |
| 2020 | yyyy | Indicates the year |
Example 3
Date: 2020/11/24
Format string: yyyy/MM/dd
|
2020
|
yyyy
|
Indicates the year
|
|
11
|
MM
|
Indicates the month
|
|
24
|
dd
|
Indicates the day
|
Example 4
Date: November 24, 2020
Format string: MMMM dd, yyyy
|
November
|
MMMM
|
Indicates the month
|
|
24
|
dd
|
Indicates the day
|
|
2020
|
yyyy
|
Indicates the year
|
Supported datetime formats
Here are a few examples of the supported datetime formats.
Example 1
Date: 11/24/2020
Format string: MM/dd/yyyy
|
11
|
MM |
Indicates the month
|
|
24
|
dd
|
Indicates the day
|
|
2020
|
yyyy
|
Indicates the year
|
Example 2
Date: 11.24.2020
Format string: MM.dd.yyyy
| 11 | MM | Indicates the month |
| 24 | dd | Indicates the day |
| 2020 | yyyy | Indicates the year |
Example 3
Date: Nov 24, 2020 02:47 PM
Format string: MMM dd, yyyy hh:mm a
| Nov | MMM | Indicates the month |
| 24 | dd | Indicates the day |
| 2020 | yyyy | Indicates the year |
|
02
|
hh
|
Indicates the hour (0-12)
|
|
47
|
mm
|
Indicates the minute
|
|
PM
|
a
|
AM/PM
|
Example 4
Format string: MMM dd, yyyy HH:mm
Date: Nov 24, 2020 14:47
| Nov | MMM | Indicates the month |
| 24 | dd | Indicates the day |
| 2020 | yyyy | Indicates the year |
| 14 | HH | Indicates the hour(00-23) |
| 47 | mm | Indicates the minute |
Example 5
Date: November 24, 2020
Format String: MMMM dd, yyyy
|
November
|
MMMM
|
Indicates the month
|
|
24
|
dd
|
Indicates the day
|
|
2020
|
yyyy
|
Indicates the year |
Example 6
Date:Nov 24, 2020 02:47 PM +5:30
Format String to be given: MMM dd, yyyy hh:mm a ZZ
|
Nov
|
MMM
|
Indicates the month
|
|
24
|
dd
|
Indicates the day
|
|
2020
|
yyyy
|
Indicates the year
|
|
02
|
hh
|
Indicates the hour (01-12)
|
|
47
|
mm
|
Indicates the minute
|
|
PM
|
a
|
AM/PM
|
|
+5.30
|
ZZ
|
Indicates the time zone
|
Example 7
Date: Wednesday, 24-Nov-98 14:47:59.163
Format String to be given: EEEE, dd-MMM-yy HH:mm:ss.SSS
|
Wednesday
|
EEEE
|
Indicates the weekday
|
|
24
|
dd
|
Indicates the day
|
|
Nov
|
MMM
|
Indicates the month
|
|
98
|
yy
|
Indicates the year
|
|
14
|
HH
|
Indicates the hour (00-23)
|
|
47
|
mm
|
Indicates the minutes
|
|
59
|
ss
|
Indicates the seconds
|
|
163
|
SSS
|
Indicates the milliseconds
|
Invalid date and datetime formats
A date or date-time format is invalid if one or more of the following conditions are not met:
- Any date format missing one or more of the Day, Month, and Year format strings is considered an invalid date format.
- For example, in the date, 25-2021, month is missing which renders it as an invalid date format.
- You can use the characters, 'Z' and 'T' only once in a date-time format.
- Examples: 2021-02-28T12:21:33T+0530, 2021-02-28T12:21:33+0530-03:30
- The format that follows the character, 'T' must be related to time such as Hours, Minutes, Seconds, etc.
- For example, in the date-time format, 28-08-T2021 12:21:33AM, T is followed by the Year component which renders it as an invalid date-time format.
- A date-time format must contain one of the Hours format.
- For example, in the date-time format, 12-05-2021 :47:59 PM, the hours renders it as an invalid date-time format.
- In a date-time format, you can use either the character, 'Z ' or 'ZZ' format strings.
- For example, in the date, Nov 24, 2020 10:00PM+0530+05:30, both 'Z' and 'ZZ' are used which makes it an invalid date-time format.
- You can have only one format from the Day, Month, Year, Hours, Minutes, and Seconds.
- For example, 01-04-2021 02:47:59PM is a valid date-time format with one format string for each date-time component.
Supported Time formats
Example 1
Time: 21:21:01
Format string: HH:mm:ss
|
21
|
HH
|
Indicates the hours(00-23)
|
|
21
|
mm
|
Indicates the minutes
|
|
01
|
ss |
Indicates the seconds
|
Example 2
Time: 11:21
Format string: HH:mm
| 11 |
HH
|
Indicates the hour(00-23)
|
|
21
|
mm
|
Indicates the minutes
|
Example 3
Time: 21:21:01.999
Format string: HH:mm:ss:SSS
|
21
|
HH
|
Indicates the hour(00-23)
|
|
21
|
mm
|
Indicates the minutes
|
|
01
|
ss
|
Indicates the seconds
|
|
999
|
SSS
|
Indicates the milliseconds
|
Example 4
Time: 11:21:01.999 AM
Format string: hh:mm:ss.SSS a
| 11 |
hh
|
Indicates the hour(01-12)
|
|
21
|
mm
|
Indicates the minutes
|
|
01
|
ss
|
Indicates the seconds
|
|
999
|
SSS
|
Indicates the milliseconds
|
|
AM
|
a
|
AM/PM |
Example 5
Time: 11:21:09 PM
Format string: hh:mm:ss a
|
11
|
hh
|
Indicates the hour(01-12) |
|
21
|
mm
|
Indicates the minutes
|
|
09
|
ss
|
Indicates the seconds
|
|
PM
|
a
|
AM/PM
|
Example 6
Time: 12:21 PM
Format string: hh:mm a
|
12
|
hh
|
Indicates the hour(01-12)
|
|
21
|
mm
|
Indicates the minutes
|
|
PM
|
a
|
AM/PM
|
Example 7
Time: 11 PM
Format string: hh a
|
11
|
hh
|
Indicates the hour(01-12)
|
|
PM
|
a
|
AM/PM
|
Invalid time formats
A time format is invalid if one or more of the following conditions are not met:
- A time format missing an Hours tile.
- For example, in the time format, :47:59 PM, the hours renders it as an invalid date-time format.
- In a time format, you can use any one of the T.zone tiles.
- For example, 12:21:33+0530+05:30, both T.zones are used which makes it an invalid time format.
- You can choose only one format from the Hours, Mins, and Sec tiles.
- For example, 02:47:59PM is a valid time format with one format string for each time component.
SEE ALSO
Access your files securely from anywhere
Zoho CRM Training Programs
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
Zoho DataPrep Personalized Demo
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
New to Zoho Writer?
Create. Review. Publish.
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
New to Zoho Survey?
Zoho Sheet Resources
Zoho Forms Resources
New to Zoho Sign?
Zoho Sign Resources
Sign, Paperless!
New to Zoho TeamInbox?
Zoho TeamInbox Resources
New to Zoho ZeptoMail?
Design. Discuss. Deliver.
New to Zoho Workerly?
New to Zoho Recruit?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?
New to Zoho Assist?
New to Bigin?
Related Articles
Duration formats supported in Zoho DataPrep
Duration is the difference between two time values. A duration format can be formed using different format strings such as D, H, SSSSSS, etc. Zoho DataPrep supports the following format strings: Format String Description Range D Indicates the Days 0 ...Unify date formats
DataPrep allows you to modify the format of the date values in a column to be in a uniform manner using the unify date format transform. To change the format of date values 1. Right-click the column with date values and select the Unify date format ...White labeled Zoho DataPrep
White labeled Zoho DataPrep makes ETL and data preparation implementation easy for independent software vendors, solution developers, business consultants, etc. by providing integrated solution into their product. You can take a look at the features ...Zoho Creator connector for Zoho DataPrep
Zoho DataPrep allows you to seamlessly bring in your forms data from Zoho Creator using the Zoho Creator connector. Zoho Creator is a powerful low-code application development platform that helps businesses build custom web and mobile apps faster. ...Ruleset in Zoho DataPrep
This page covers the following sections: What is a Ruleset? Data source settings Ruleset operations Options with individual rules Ruleset Templates What is a Ruleset? Each transform applied on the data is stored in DataPrep as a rule, in order of ...
New to Zoho LandingPage?
Zoho LandingPage Resources