Skip to main content

Celonis Product Documentation

DATE_BETWEEN
Description

DATE_BETWEEN determines whether the first date is between the second date and the third date.

The left boundary specified by the second date is inclusive and the right boundary specified by the third date is exclusive. Therefore the function returns 1 if the first date is in range [secondDate, thirdDate), and 0 otherwise.

Supported input column types: DATE

Output column type: INT, values 1 for true or 0 for false

Syntax
  DATE_BETWEEN ( table.column1, table.column2, table.column3 )
NULL handling

If any parameter is NULL, the result is NULL as well.

Example

[1]

DATE_BETWEEN of two rows. In both cases, the first date is between the second date and the third date

Query

Column1 

         DATE_BETWEEN ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" )

Input

Output

Table1

Column1 : date

Column2 : date

Column3 : date

Sat Jan 01 2000 00:00:00.000

Fri Dec 31 1999 23:59:59.999

Mon May 09 2005 23:59:59.000

Mon May 09 2005 23:59:59.000

Sat Jan 01 2000 00:00:00.000

Mon May 09 2005 23:59:59.100

Result

Column1 : int

1

1

[2]

DATE_BETWEEN of two rows. In the first case the first date is equal to the second date and less than the third date. In the second case the first date is greater than the second date and equal to the third date.

Query

Column1 

         DATE_BETWEEN ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" )

Input

Output

Table1

Column1 : date

Column2 : date

Column3 : date

Sat Jan 01 2000 00:00:00.000

Sat Jan 01 2000 00:00:00.000

Mon May 09 2005 23:59:59.000

Sat Jan 01 2000 00:00:00.000

Fri Dec 31 1999 23:59:59.999

Sat Jan 01 2000 00:00:00.000

Result

Column1 : int

1

0

See also: