Skip to main content

Celonis Product Documentation

TO_DATE
Description

TO_DATE converts STRING input to DATE output. You must define the FORMAT. If the input does not match the FORMAT, the function returns NULL.

Supported input column type: STRING.

Output column type: DATE.

Syntax
 TO_DATE ( table.column, FORMAT ( format ) )
FORMAT syntax

The <format> defines how the input STRING should be interpreted.

The following special fields in <format> are supported:

  • %Y: 4 digit year.

  • %m: 1-2 digit month of year. A leading zero is permitted, but not required.

  • %d: 1-2 digit day of month. A leading zero is permitted, but not required.

  • %H: 1-2 digit hour of day in 24-hour format. A leading zero is permitted, but not required.

  • %M: 1-2 digit minutes per hour. A leading zero is permitted, but not required.

  • %S: 1-2 digit seconds per minute. A leading zero is permitted, but not required.

  • %F: 1-3 digit milliseconds per second. Leading zeroes are permitted, but not required.

  • %%: A literal "%" character.

All other FORMAT content must exactly match the input STRING. If the input value does not fit the given FORMAT, the output value will be NULL.

All parts of the timestamp which are not set in the pattern will be set to their base value. The base values are:

  • Year: 1970

  • Month: 01

  • Day: 01

  • Hour: 00

  • Minute: 00

  • Second: 00

  • Milliseconds: 000

Null handling

If the input value is NULL, the output value is NULL as well.

Examples

[1]

Example in which a STRING constant is converted to a DATE type.

Query

Column1

         TO_DATE ( '2017-03-14 11:22:33' , FORMAT ( '%Y-%m-%d %H:%M:%S' ) )
        

Column2

         TO_DATE ( '2017-03-14 11:22:33:444' , FORMAT ( '%Y-%m-%d %H:%M:%S:%F' ) )
        

Input

Output

Result

Column1 : date

Column2 : date

Tue Mar 14 2017 11:22:33.000

Tue Mar 14 2017 11:22:33.444

[2]

Example in which STRING inputs with different digit widths are converted to the DATE type.

Query

Column1

         TO_DATE ( "Table"."Timestamp" , FORMAT ( '%Y-%m-%d %H:%M:%S:%F' ) )
        

Input

Output

Table

Timestamp : string

'2017-03-14 01:02:03:004'

'2017-03-14 1:2:3:4'

'2017-3-14 11:22:33:444'

'2017-03-1 11:22:33:44'

'2017-3-1 1:2:3:4'

Result

Column1 : date

Tue Mar 14 2017 01:02:03.004

Tue Mar 14 2017 01:02:03.004

Tue Mar 14 2017 11:22:33.444

Wed Mar 01 2017 11:22:33.044

Wed Mar 01 2017 01:02:03.004

[3]

Example in which only hours and minutes without a date are contained in the STRING constant. The date is set to the base date 01.01.1970 in the resulting DATE value, and the seconds and milliseconds are both set to 0. Leading zeros for hour and minute values are permitted, but not required:

Query

Column1

         TO_DATE ( "Table"."Timestamp" , FORMAT ( '%H:%M' ) )
        

Input

Output

Table

Timestamp : string

'23:10'

'2:10'

'12:3'

'02:5'

Result

Column1 : date

Thu Jan 01 1970 23:10:00.000

Thu Jan 01 1970 02:10:00.000

Thu Jan 01 1970 12:03:00.000

Thu Jan 01 1970 02:05:00.000