Filtering by Date
It is common to convert selected records based on a date or dates in a field. For example, you may run a transformation on a daily basis and only want to transform those records that contain today's date. Dates may appear in a wide variety of formats in data files, such as the following:
• Type of data file - Pre-structured data files where dates are stored in only one format, such as dBASE; binary files; record manager files; spreadsheets; ASCII files
• Typical date format - "yyyymmdd" (stored as a "Date" type); "Text", "Integer", or "Date" types; "mm/dd/yy", "yy/mm/dd", or "dd-Mon-yyyy"
Example of Filtering Two or More Date Fields
'Both fields are defined as Date. In this case, you only want records that contain "19941215" in ("FIELD1") and "19940915" in ("FIELD2").
DateValMask(FieldAt("/SOURCE/R1/Field1"), "yyyymmdd") = "19941215" And DateValMask(FieldAt("/SOURCE/R1/Field2"), "yyyymmdd") = "19940915"
Example of Filtering Records Based on the Date in a Single Field
'The field is defined as Text, where the source type is a Fixed ASCII file, and where you only want records that contain dates between "05/01/1994" and "05/05/1994", inclusive, in ("FIELD1").
DateValue(Trim(FieldAt("/SOURCE/R1/Field1"))) >= DateValue(Trim("05/01/1994")) And DateValue(Trim(FieldAt("/SOURCE/R1/Field1"))) <= DateValue(Trim("05/05/1994"))
Example of Filtering Records Based on Dates in Two Fields
'The fields are both defined as Date, where you want only records where the contents of Field1 are greater than or equal to the contents of Field2.
FieldAt("/SOURCE/R1/Field1") >= FieldAt("/SOURCE/R1/Field2")
Last modified date: 02/09/2024