ToDate

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.