ETL: ToDate

Convert an input string to a datetime data type based on a specified format pattern. Use TODATE for date parsing in Integrate.io ETL pipelines.

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.