Skip to main content

Celonis Product Documentation

DATEDIFF
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 as well.

Examples

[1]

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

Query

Column1 

         DATEDIFF ( ms , "Table1"."Column1" , "Table1"."Column2" )

Input

Output

Table1

Column1 : date

Column2 : 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

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

Output

Table1

Column1 : date

Column2 : 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

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

Output

Table1

Column1 : date

Column2 : 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

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

Output

Table1

Column1 : date

Column2 : 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

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

Output

Table1

Column1 : date

Column2 : 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

Result

Column1 : float

0.0

1.0

0.5

3.0

2.5

[6]

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

Query

Column1 

         DATEDIFF ( mm , "Table1"."Column1" , "Table1"."Column2" )

Input

Output

Table1

Column1 : date

Column2 : 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

Result

Column1 : float

0.0

0.5

1.0

132.0

131.0

[7]

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

Query

Column1 

         DATEDIFF ( yy , "Table1"."Column1" , "Table1"."Column2" )

Input

Output

Table1

Column1 : date

Column2 : 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

Result

Column1 : float

0.0

0.41889977

1.0

2.0

1.0