ETL: ParseDate

Convert an input datetime string to a datetime data type using the first matching format from a list of patterns. Flexible date parsing for ETL.

Description

Convert input datetime string to datetime data type, using the first format that matches the string.

Syntax

ParseDate(datetime_string, format_string_1, ..., format_string_n)

Arguments

datetime_string - datetime value in a string expression in a custom format

format_string_[1..N] - format of the custom string. See below for formatting information.

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

ParseDate('2015-05-02','dd/MM/yyyy','yyyy-MM-dd') returns a datetime expression 2015-05-02T00:00:00Z

ParseDate('2019-03-17T12:11:04 +06:00', 'yyyy-MM-dd\'T\'HH:mm:ss Z')returns a datetime expression 2019-03-17T06:11:04.000Z

ParseDate('Hi!','dd/MM/yyyy','yyyy-MM-dd') returns a datetime null expression.

Return value datatype

datetime

Impact of null value

If custom_string is null, null is returned.
If format strings are null, an exception is raised.
If no format matches the input string, null is returned.