Convert input, based on format, to datetime data type.




ToDate(custom_string, format)

ToDate(custom_string, format, timezone)


  milliseconds - long. The number of milliseconds from 1970-01-01T00:00:00.000Z

  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


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('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).

Return value datatype



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.

Feedback and Knowledge Base