Skip to main content

Celonis Product Documentation

DATE_MATCH
Description

DATE_MATCH returns 1 if the input date is part of each non empty filter list. Each filter list represents a date type. The first list represents a set of years, which is filtered for. The second a list of quarters and so on. An empty list is not considered for filtering.

Syntax
  DATE_MATCH(column, [YEARS], [QUARTERS], [MONTHS], [WEEKS], [DAYS])
NULL handling

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

Examples

[1]

Match date with specifying all filter lists.

Query

Column1 

         DATE_MATCH ( "Table1"."Column1" , [ 2008 ] , [ 1 , 2 ] , [ 1 , 2 , 3 , 5 ] , [ 1 , 6 , 11 , 21 ] , [ 1 , 8 , 15 , 22 ] )

Input

Output

Table1

Column1 : date

Tue Jan 01 2008 00:00:00.000

Fri Feb 08 2008 00:00:00.000

Sat Mar 15 2008 00:00:00.000

Thu May 22 2008 00:00:00.000

Result

Column1 : int

1

1

1

1

[2]

Filter lists don't match any date

Query

Column1 

         DATE_MATCH ( "Table1"."Column1" , [ 2008 ] , [ 1 , 2 ] , [ 1 , 2 , 3 , 5 ] , [ 1 , 6 , 11 , 21 ] , [ 1 , 8 , 15 , 22 ] )

Input

Output

Table1

Column1 : date

Mon Jan 01 2007 00:00:00.000

Sat Mar 08 2008 00:00:00.000

Sun Mar 16 2008 00:00:00.000

Mon Jun 22 2009 00:00:00.000

Result

Column1 : int

0

0

0

0

[3]

Match date with empty filter lists for quarters and weeks.

Query

Column1 

         DATE_MATCH ( "Table1"."Column1" , [ 2008 ] , [ ] , [ 1 , 2 , 3 , 5 ] , [ ] , [ 1 , 8 , 15 , 22 ] )

Input

Output

Table1

Column1 : date

Tue Jan 01 2008 00:00:00.000

Fri Feb 08 2008 00:00:00.000

Sat Mar 15 2008 00:00:00.000

Thu May 22 2008 00:00:00.000

Result

Column1 : int

1

1

1

1

[4]

Non-empty filter lists don't match any date.

Query

Column1 

         DATE_MATCH ( "Table1"."Column1" , [ 2008 ] , [ ] , [ ] , [ ] , [ 1 , 2 , 3 , 4 ] )

Input

Output

Table1

Column1 : date

Mon Jan 01 2007 00:00:00.000

Tue Feb 05 2008 00:00:00.000

Mon Mar 10 2008 00:00:00.000

Fri May 15 2009 00:00:00.000

Result

Column1 : int

0

0

0

0