Date variables
For each Date or Date and Time attribute on the Fact Transform business object, the system creates six Kettle variables.
The following table summarizes these Kettle variables:
| Kettle Variable | Description |
|---|---|
| ${triActiveStartDA} | No suffix = is the value in milliseconds since January 1, 2014, with no changes to the time. This variable is for fields that are represented as a number. |
| ${triActiveStartDA_Min} | Min = is the value in milliseconds since January 1, 2014, with the time value set to 00:00:00 for the specified date. This variable is for fields that are represented as a number. |
| ${triActiveStartDA_Max} | Max = is the value in milliseconds since January 1, 2014, with the time value set to 23:59:59 for the specified date. This variable is for fields that are represented as a number. |
| ${triActiveStartDA_DATE} | DATE = is the wrapped value in date format,
with no changes to the time. This variable is for fields that are
represented as date in the database. For Oracle or DB2 it is wrapped and displays like: to_date (‘20070615 22:45:10’,’YYYYmmdd h24:mi:ss’) For SQL Server it displays like: ‘20070615 22:45:10’ |
| ${triActiveStartDA_MinDATE} | MinDATE = is the wrapped value in date format, with the time value set to 00:00:00. This variable is for fields that are represented as date in the database. |
| ${triActiveStartDA_MaxDATE} | MaxDATE = is the wrapped value in date format, with the time value set to 23:59:59. This variable is for fields that are represented as date in the database. |
When you specify the ${triActiveStartDA_Min} and ${triActiveStartDA_Max} variables to see a time period between two dates, you need to capture all the rows within the time period. You need to start at midnight and stop at 1 second before midnight. If you use only the date value, you might not get all the rows that you want, depending on the time on the variable. You must specify the minutes and seconds because both TRIRIGA® databases store dates in a date time or number field.
The ${triActiveStartDA_MinDATE} and ${triActiveStartDA_MaxDATE} variables help with date comparisons.
For example, for triActiveStartDA whose value is 20070615 22:45:10,
triActiveStartDA_MinDATE =
(Oracle) to_date(‘20070615 00:00:00’,’YYYYmmdd h24:mi:ss’)
(SQL Server) ‘20070615 00:00:00’
triActiveStartDA_MaxDATE =
(Oracle) to_date(‘20070615 23:59:59’,’YYYYmmdd h24:mi:ss’)
(SQL Server) ‘20070615 23:59:59’