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.
