TODATE Function

Parameters (String, String, [String]);

Returns: Timestamp(DateTime), Date or Time

Outputs the date/time created by formatting the given string to the given pattern.

The first parameter is the string from which the date/time will be created.

The second parameter is the format pattern to be used.

The optional third parameter is a string indicating the TYPE of format/value used. This must be one of DATE, TIME or DATETIME. This also identifies the type of value that will be returned. If this parameter is omitted, DATETIME is assumed.

An error will occur if the value and type do not match the format string given, or if the format string is invalid. An error will also occur if the return TYPE cannot be cast to the expected type in the context of the mapping.

For example, using the following assignment function to set a target value of type Date will result in error, since TIME cannot be cast to DATE: TODATE('13:00','HH:mm','TIME')

The examples below show some examples of how this function can be used.

Example 1:  TODATE('20150101','yyyyMMdd','DATE') = 2015-07-15 00:00:00

Example 2:  TODATE('07/11/15 17:10','dd/MM/yy HH:mm','DATETIME') =  2015-11-07 17:10:00

Example 3:  TODATE('23 Apr, 2018','dd MMM,yyyy','DATE') = 2018-04-21 00:00:00

Example 4:  TODATE('20150609T100000','yyyyMMdd''T''hhmmss',DATETIME') = 2015-06-09 10:00:00

Example 5:  TODATE('Jul 7, 2015 1:10:05 PM EST','MMM d, yyyy h:mm a zz',DATETIME') = 2015-07-15 13:10:05

Example 6:  TODATE('2015-01-01 13:00','yyyyMMdd','DATETIME') = ERROR - value does not match format

Example 7:  TODATE('2015-01-01 13:00','yyyyMMdd HH:mm','DATE') = ERROR - type does not match format