Show TOC

Function documentationRelative Date Filtering

 

You can enter specific values to define relative filters for dates, which can then be used for time-dependent comparisons. For example, you might want to compare new documents created on today’s date.

To perform relative date filtering, that is, to calculate relative dates instead of absolute dates, cross-database comparison provides syntax for defining fixed points and offsets for relative dates.

Prerequisites

In the details popup for a mapped field or a source-specific field, you have set the filter type to Relative Time Stamp.

Features

  • Instead of a fixed (absolute) date, you can enter a keyword for the start date (using $ as a prefix) and optionally an additional offset for the difference in days:

    Syntax = <StartDate>[<Difference>]

    You can replace <StartDate> with the following keywords:

    Keyword for <StartDate>

    Description

    Example Using 2013-04-25 as a Reference Date

    $TODAY

    Current date (today)

    2013-04-25

    $FDOCW

    First day of current week

    2013-04-22

    $LDOCW

    Last day of current week

    2013-04-28

    $FDOCM

    First day of current month

    2013-04-01

    $LDOCM

    Last day of current month

    2013-04-30

    $FDOCY

    First day of current year

    2013-01-01

    $LDOCY

    Last day of current year

    2013-12-31

    $FDOPW

    First day of previous week

    2013-04-15

    $LDOPW

    Last day of previous week

    2013-04-21

    $FDOPM

    First day of previous month

    2013-03-01

    $LDOPM

    Last day of previous month

    2013-03-31

    $FDOPY

    First day of previous year

    2012-01-01

    $LDOPY

    Last day of previous year

    2012-12-31

    $FDONW

    First day of next week

    2013-04-29

    $LDONW

    Last day of next week

    2013-05-05

    $FDONM

    First day of next month

    2013-05-01

    $LDONM

    Last day of next month

    2013-05-31

    $FDONY

    First day of next year

    2014-01-01

    $LDONY

    Last day of next year

    2014-12-31

    $TIMES

    Time stamp now (offset in seconds)

    n/a

    $TIMEM

    Time stamp now (offset in minutes)

    n/a

    $TIMEH

    Time stamp now (offset in hours)

    n/a

    $TIMED

    Time stamp now (offset in days)

    n/a

    $DELTA

    Delta mode (timestamp of last run)

    n/a

    Note Note

    These dates are based on the Gregorian calendar and as such, Monday is considered to be the first day of the week.

    End of the note.

  • You can enter the <difference> as a positive or negative offset in days using the following signs:

    • The plus sign (+) increases days, that is, the start date is moved into the future.

    • The minus sign (-) decreases days, that is, the start date is moved into the past.

    Example Example

    $TODAY-2: day before yesterday

    $TODAY+2: day after tomorrow

    End of the example.

  • You can combine the relative date selection in ranges using the From and To fields of the selection criteria, for example, to define intervals.

    Example Example

    To count the number of documents that were created in the last month, you enter the following selection criteria: <Field Name> = $FDOPM to $LDOPM.

    End of the example.

  • For the ABAP source type, filter-fields with the following data types support the syntax for relative dates:

    DDic Data Type

    ABAP Data Type

    Date Format

    DATS

    D

    YYYYMMDD

    CHAR 10

    C 10

    YYYYMMDD

    CHAR 14

    C 14

    YYYYMMDDhhmmss

    DEC 15

    P 8

    YYYYMMDDhhmmss

    DEC 21

    P 11

    YYYYMMDDhhmmss,mmmuuun

    TIMS

    C 6

    hhmmss

    Legend:

    • YYYY = year

    • MM = month

    • DD = day

    • hh = hour

    • mm = minute

    • ss = second

    • mmmuuun = milliseconds / microseconds / nanoseconds

    Note Note

    In data types containing time stamps (hhmmss), the system enters 000000 by default.

    Exception: For start date keywords using a “Last Day” option (for example, $LDOCM), a maximum time stamp (235959) is set by the system.

    The keywords $TIME<x> and $DELTA are filled with exact values.

    End of the note.

  • You can also use relative date filtering for the ADBC source type. However, the supported date and time-stamp data types depend on the database management system that is used.

  • For the $TIME<x> and $DELTA keywords, the system calculates an exact time and not only a date.

    The following keywords use the current system time stamp at the time of data comparison: $TIMES, $TIMEM, $TIMEH, and $TIMED. The system calculates exact time stamps using DEC 15 and DEC 21 data types (see table above). You can use different time units for the offset, for example, seconds, minutes, hours, or days.

    $DELTA stores the time stamp of each data comparison run, and at the next run, it selects the data created since the last run by entering >=$DELTA in the time stamp filter. You can enter an additional offset in minutes (plus or minus <Difference>).

    Both keywords support an additional <Difference> offset. For $DELTA , the value entered is interpreted as minutes.

    The time stamp is calculated based on UTC time. If the database table that is used stores time stamps in a different time format, you can use the <Difference> operator to adjust the value.