Skip to main content

Celonis Product Documentation

TO_TIMESTAMP
Deprecated

Warning

Please use TO_DATE instead.

Description

TO_TIMESTAMP converts a string into a DATE. The format of the input string can be defined.

The following fields are supported to define the input format:

  • YYYY: 4 digit year

  • MM: 2 digit month of year

  • DD: 2 digit day of month

  • HH: 2 digit hour of day in 24 hour format

  • mm: 2 digit minutes per hour

  • SS: 2 digit seconds per minute

All other characters are interpreted as separators. TO_TIMESTAMP doesn't check if the separators actually match. TO_TIMESTAMP just skips the separators characters.

The number of digits per field has to exactly match. For example the 2 digit month field MM has to have exactly two digits and can not have only one. In case of an mismatch the operator returns NULL and throws a warning.

Unicode support: TO_TIMESTAMP is only guaranteed to work as expected when used on US-ASCII strings. This restriction applies to both the STRING input and the <format> string.

Syntax
  TO_TIMESTAMP ( table.column, format )
 
NULL handling

If the input value is NULL, then the result is NULL as well.

Examples

[1]

Example in which a string containing a date is transformed to an actual DATE type.

Query

Column1

         TO_TIMESTAMP ( "Table1"."Column1" , 'DD.MM.YYYY' )
        

Input

Output

Table1

Column1 : string

'01.10.2016'

'11.10.2016'

'21.10.2016'

Result

Column1 : date

Sat Oct 01 2016 00:00:00.000

Tue Oct 11 2016 00:00:00.000

Fri Oct 21 2016 00:00:00.000

[2]

Example in which a string containing a timestamp is transformed to an actual DATE type.

Query

Column1

         TO_TIMESTAMP ( "Table1"."Column1" , 'DD.MM.YYYY HH:mm:SS' )
        

Input

Output

Table1

Column1 : string

'01.10.2016 01:05:09'

'11.10.2016 02:06:10'

'21.10.2016 03:07:11'

'31.10.2016 04:08:12'

Result

Column1 : date

Sat Oct 01 2016 01:05:09.000

Tue Oct 11 2016 02:06:10.000

Fri Oct 21 2016 03:07:11.000

Mon Oct 31 2016 04:08:12.000

[3]

This example shows that TO_TIMESTAMP doesn't check the separators. Therefore varying separators can be in the input data.

Query

Column1

         TO_TIMESTAMP ( "Table1"."Column1" , 'DD.MM.YYYY' )
        

Input

Output

Table1

Column1 : string

'01.10.2016'

'11/10/2016'

'21-10-2016'

Result

Column1 : date

Sat Oct 01 2016 00:00:00.000

Tue Oct 11 2016 00:00:00.000

Fri Oct 21 2016 00:00:00.000

[4]

Separators are not necessary as shown here.

Query

Column1

         TO_TIMESTAMP ( "Table1"."Column1" , 'DDMMYYYY' )
        

Input

Output

Table1

Column1 : string

'01102016'

'11102016'

'21102016'

Result

Column1 : date

Sat Oct 01 2016 00:00:00.000

Tue Oct 11 2016 00:00:00.000

Fri Oct 21 2016 00:00:00.000