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.