Applies to:  CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 

Description

DATEDIFF computes the difference between two dates in the specified time unit.

Supported input column types: DATE

Output column type: FLOAT

Syntax

DATEDIFF ( time_unit, table.date_column1, table.date_column2 )
  • time_unit: ms | ss | mi | hh | dd | mm | yy
    • ms: Calculate the difference between the given dates in milliseconds (equals MILLIS_BETWEEN)
    • ss: Calculate the difference between the given dates in seconds (equals SECONDS_BETWEEN)
    • mi: Calculate the difference between the given dates in minutes (equals MINUTES_BETWEEN)
    • hh: Calculate the difference between the given dates in hours (equals HOURS_BETWEEN)
    • dd: Calculate the difference between the given dates in days (equals DAYS_BETWEEN)
    • mm: Calculate the difference between the given dates in months (equals MONTHS_BETWEEN)
    • yy: Calculate the difference between the given dates in years (equals YEARS_BETWEEN)

Null handling

If any parameter is NULL, the result is NULL

Examples


[1] Calculate the difference between dates of two rows in milliseconds (equals MILLIS_BETWEEN):

Query
Column1
DATEDIFF ( ms , "Table1"."Column1" , "Table1"."Column2")
Input
Table1
Column1 : DATEColumn2 : DATE
Sat Jan 01 2000 00:00:00.000
Fri Dec 31 1999 23:59:59.999
Mon May 09 2005 12:01:10.000
Mon May 09 2005 12:01:11.000
Output
Result
Column1 : FLOAT
-1.0
1000.0



[2] Calculate the difference between dates of two rows in seconds (equals SECONDS_BETWEEN):

Query
Column1
DATEDIFF ( ss , "Table1"."Column1" , "Table1"."Column2")
Input
Table1
Column1 : DATEColumn2 : DATE
Sat Jan 01 2000 00:00:59.999
Fri Dec 31 1999 23:59:59.999
Mon May 09 2005 12:01:10.000
Mon May 09 2005 12:01:11.500
Output
Result
Column1 : FLOAT
-60.0
1.5



[3] Calculate the difference between dates of two rows in minutes (equals MINUTES_BETWEEN):

Query
Column1
DATEDIFF ( mi , "Table1"."Column1" , "Table1"."Column2")
Input
Table1
Column1 : DATEColumn2 : DATE
Sat Jan 01 2000 01:00:29.999
Fri Dec 31 1999 23:59:59.999
Mon May 09 2005 12:00:00.000
Mon May 09 2005 00:00:00.000
Output
Result
Column1 : FLOAT
-60.5
-720.0



[4] Calculate the difference between dates of two rows in hours (equals HOURS_BETWEEN):

Query
Column1
DATEDIFF ( hh , "Table1"."Column1" , "Table1"."Column2")
Input
Table1
Column1 : DATEColumn2 : DATE
Sat Jan 01 2000 23:29:59.999
Fri Dec 31 1999 23:59:59.999
Mon May 09 2005 12:00:00.000
Mon May 09 2005 00:00:00.000
Output
Result
Column1 : FLOAT
-23.5
-12.0



[5] Calculate the difference between dates of five rows in days (equals DAYS_BETWEEN):

Query
Column1
DATEDIFF ( dd , "Table1"."Column1" , "Table1"."Column2")
Input
Table1
Column1 : DATEColumn2 : DATE
Fri Dec 31 1999 23:59:59.000
Fri Dec 31 1999 23:59:59.000
Fri Dec 31 1999 23:59:59.000
Sun Jan 02 2000 00:01:02.000
Fri Dec 31 1999 23:59:59.000
Sat Jan 01 2000 12:01:00.000
Fri Dec 31 1999 23:59:59.000
Tue Jan 04 2000 00:00:00.000
Sat Jan 01 2000 12:01:00.000
Tue Jan 04 2000 00:00:00.000
Output
Result
Column1 : FLOAT
0.0
1.0007291666666667
0.5007060185185186
3.0000115740740743
2.4993055555555554



[6] Calculate the difference between dates of five rows in months (equals MONTHS_BETWEEN):

Query
Column1
DATEDIFF ( mm , "Table1"."Column1" , "Table1"."Column2")
Input
Table1
Column1 : DATEColumn2 : DATE
Fri Dec 31 1999 23:59:59.000
Fri Dec 31 1999 23:59:59.000
Fri Dec 31 1999 23:59:59.000
Sat Jan 15 2000 00:00:00.000
Fri Dec 31 1999 23:59:59.000
Mon Jan 31 2000 00:00:00.000
Fri Dec 31 1999 23:59:59.000
Fri Dec 31 2010 00:00:00.000
Mon Jan 31 2000 00:00:00.000
Fri Dec 31 2010 00:00:00.000
Output
Result
Column1 : FLOAT
0.0
0.49282326125793136
1.0185014065997249
132.01092424895788
130.99242284235817



[7] Calculate the difference between dates of five rows in years (equals YEARS_BETWEEN):

Query
Column1
DATEDIFF ( yy , "Table1"."Column1" , "Table1"."Column2")
Input
Table1
Column1 : DATEColumn2 : DATE
Fri Dec 31 1999 23:59:59.000
Fri Dec 31 1999 23:59:59.000
Fri Dec 31 1999 23:59:59.000
Thu Jun 01 2000 00:00:00.000
Fri Dec 31 1999 23:59:59.000
Sun Dec 31 2000 00:00:00.000
Fri Dec 31 1999 23:59:59.000
Mon Dec 31 2001 00:00:00.000
Sun Dec 31 2000 00:00:00.000
Mon Dec 31 2001 00:00:00.000
Output
Result
Column1 : FLOAT
0.0
0.41889977206924167
1.0020739645577939
2.001410022108599
0.9993360575508052


  • No labels