Skip to main content

Celonis Product Documentation

TO_DATE
Description

TO_DATE converts STRING input to DATE output. The FORMAT must be defined. If the input does not match the FORMAT, NULL is returned.

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. Leading zero is permitted, but not required.

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

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

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

  • %S: 1-2 digit seconds per minute. Leading zero is 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

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

Input

Output

Result

Column1 : date

Tue Mar 14 2017 11:22:33.000

[2]

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

Query

Column1

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

Input

Output

Table

Timestamp : string

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

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

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

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

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

Result

Column1 : date

Tue Mar 14 2017 01:02:03.000

Tue Mar 14 2017 01:02:03.000

Tue Mar 14 2017 11:22:33.000

Wed Mar 01 2017 11:22:33.000

Wed Mar 01 2017 01:02:03.000

[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