Description
Converts input, based on format, to datetime data type.
Syntax
-
ToDate(milliseconds)
-
ToDate(datetime_string)
-
ToDate(custom_string, format)
ToDate(custom_string, format, timezone)
Arguments
-
milliseconds - long. The number of milliseconds from 1970-01-01T00:00:00.000Z (similar to Unix time except that Unix is the number of seconds instead of milliseconds.)
-
datetime_string - string expression in ISO 8601 format. If the value of the expression is not in the ISO 8601 format, an exception is thrown.
-
custom_string - datetime value in a string expression in a custom format
-
format - format of the custom string. See below for formatting information.
-
timezone - timezone information in either UTC offset or location based format.
Format strings
| Symbol |
Meaning |
Presentation |
Examples |
| G |
era |
text |
AD |
| C |
century of era (>=0) |
number |
20 |
| Y |
year of era (>=0) |
year |
1996 |
|
|
|
|
| x |
weekyear |
year |
1996 |
| w |
week of weekyear |
number |
27 |
| e |
day of week |
number |
2 |
| E |
day of week |
text |
Tuesday; Tue |
|
|
|
|
| y |
year |
year |
1996 |
| D |
day of year |
number |
189 |
| M |
month of year |
month |
July; Jul; 07 |
| d |
day of month |
number |
10 |
|
|
|
|
| a |
halfday of day |
text |
PM |
| K |
hour of halfday (0~11) |
number |
0 |
| h |
clockhour of halfday (1~12) |
number |
12 |
|
|
|
|
| H |
hour of day (0~23) |
number |
0 |
| k |
clockhour of day (1~24) |
number |
24 |
| m |
minute of hour |
number |
30 |
| s |
second of minute |
number |
55 |
| S |
fraction of second |
number |
978 |
|
|
|
|
| z |
time zone |
text |
Pacific Standard Time; PST |
| Z |
time zone offset/id |
zone |
-0800; -08:00; America/Los_Angeles |
|
|
|
|
| \' |
escape character |
for a-z/A-Z |
T |
| \'\' |
single quote |
literal single quote |
' |
Based on http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html
Examples
-
ToDate('2013-10-17T23:59:54.432Z') returns datetime value 2013-10-17T23:59:54.432Z
-
ToDate(0) returns datetime value 1970-01-01T00:00:00.000Z
-
ToDate(1414877400 * 1000) returns datetime value 2014-11-01T21:30:00.000Z (takes the Unix timestamp of 1414877400 which is in seconds and multiplies it by 1000 to generate the number of milliseconds that the ToDate function requires)
-
ToDate(1585195200000) returns datetime value 2020-03-26T04:00:00.000Z . (When run in X-Console, explicitly cast that number as a Long datatype like this: ToDate(1585195200000L))
-
ToDate('10/17/2013 23:59:54','MM/dd/yyyy HH:mm:ss') returns datetime value 2013-10-17T23:59:54.000Z
-
ToDate('10/17/2013 23:59:54','MM/dd/yyyy HH:mm:ss','+01:00') returns datetime value 2013-10-17T23:59:54.000+01:00
-
ToDate('Tue Jul 02 09:18:52 +0000 2013','EEE MMM dd HH:mm:ss Z yyyy') returns datetime value 2013-07-02T09:18:52.000Z (This is the datetime format used in Twitter stream).
-
ToDate('2019-03-17T12:11:04 +06:00', 'yyyy-MM-dd\'T\'HH:mm:ss Z') returns 2019-03-17T06:11:04.000Z
Return value datatype
datetime
Notes
If format or timezone are null, an exception will be thrown.
Impact of null value
If milliseconds, iso8601_string or custom_string are null, returns null.