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

Date Match

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])

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
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
Output
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
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
Output
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
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
Output
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
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
Output
Result
Column1 : INT
0
0
0
0


  • No labels