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 value2014-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 value2020-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 value2013-10-17T23:59:54.000Z
-
ToDate('10/17/2013 23:59:54','MM/dd/yyyy HH:mm:ss','+01:00')
returns datetime value2013-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 value2013-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')
returns2019-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.