Applies to:  CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 

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
dd
2 digit day of month
HH
2 digit hour of day in 24 hour format
mm
2 digit month
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 aborts with an error.

Syntax

TO_TIMESTAMP ( table.column, format )

Examples

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
Table1
Column1 : STRING
'01.10.2016'
'11.10.2016'
'21.10.2016'
Output
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



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
Table1
Column1 : STRING
'01.10.2016'
'11/10/2016'
'21-10-2016'
Output
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



Separators are not necessary as shown here.
Query
Column1
TO_TIMESTAMP("Table1"."Column1", 'DDMMYYYY' )
Input
Table1
Column1 : STRING
'01102016'
'11102016'
'21102016'
Output
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



  • No labels