In this section: |
New date and date-time functions have been developed that make it easier to understand and enter the required arguments. These functions have streamlined parameter lists, similar to those used by SQL functions. In some cases, these simplified functions provide slightly different functionality than previous versions of similar functions.
The simplified functions do not have an output argument. Each function returns a value that has a specific data type.
When used in a request against a relational data source, these functions are optimized (passed to the RDBMS for processing).
Standard date and date-time formats refer to YYMD and HYYMD syntax (dates that are not stored in alphanumeric or numeric fields). Dates not in these formats must be converted before they can be used in the simplified functions. Literal date-time values can be used with the DT function.
All arguments can be either literals, field names, or amper variables.
How to: Reference: |
Given a date in standard date or date-time format, DTADD returns a new date after adding the specified number of a supported component. The returned date format is the same as the input date format.
DTADD(date, component, increment)
where:
Date or date-time
Is the date or date-time value to be incremented.
Keyword
Is the component to be incremented. Valid components (and acceptable values) are:
Integer
Is the value (positive or negative) to add to the component.
The following request against the WF_RETAIL data source adds three days to the employee date of birth:
DEFINE FILE WF_RETAIL NEWDATE/YYMD = DTADD(DATE_OF_BIRTH, DAY, 3); MGR/A3 = DIGITS(ID_MANAGER, 3); END TABLE FILE WF_RETAIL SUM MGR NOPRINT DATE_OF_BIRTH NEWDATE BY MGR ON TABLE SET PAGE NOPAGE END
The output is:
How to: |
Given two dates in standard date or date-time formats, DTDIFF returns the number of given component boundaries between the two dates. The returned value has integer format for calendar components or double precision floating point format for time components.
DTDIFF(end_date, start_date, component)
where:
Date or date-time
Is the ending date in either standard date or date-time format. If this date is given in standard date format, all time components are assumed to be zero.
Date or date-time
Is the starting date in either standard date or date-time format. If this date is given in standard date format, all time components are assumed to be zero.
Keyword
Is the component on which the number of boundaries is to be calculated. For example, QUARTER finds the difference in quarters between two dates. Valid components (and acceptable values) are:
The following request against the WF_RETAIL data source calculates employee age when hired:
DEFINE FILE WF_RETAIL YEARS/I9 = DTDIFF(START_DATE, DATE_OF_BIRTH, YEAR); END TABLE FILE WF_RETAIL PRINT START_DATE DATE_OF_BIRTH YEARS AS 'Hire,Age' BY EMPLOYEE_NUMBER WHERE EMPLOYEE_NUMBER CONTAINS 'AA' ON TABLE SET PAGE NOPAGE END
The output is:
How to: |
Given a date in standard date or date-time format and a component, DTPART returns the component value in integer format.
DTPART(date, component)
where:
Date or date-time
Is the date in standard date or date-time format.
Keyword
Is the component to extract in integer format. Valid components (and values) are:
The following request against the WF_RETAIL data source extracts the QUARTER component from the employee start date:
DEFINE FILE WF_RETAIL QTR/I2 =DTPART(START_DATE, QUARTER); END TABLE FILE WF_RETAIL PRINT START_DATE QTR AS Quarter BY EMPLOYEE_NUMBER WHERE EMPLOYEE_NUMBER CONTAINS 'AH' ON TABLE SET PAGE NOPAGE END
The output is:
How to: |
Given a date or timestamp and a component, DTRUNC returns the first date within the period specified by that component.
DTRUNC(date_or_timestamp, date_period)
where:
Date or date-time
Is the date or timestamp of interest.
Is the period whose starting date you want to find. Can be one of the following:
In the following request against the WF_RETAIL data source, DTRUNC returns the first date of the quarter given the start date of the employee:
DEFINE FILE WF_RETAIL QTRSTART/YYMD = DTRUNC(START_DATE, QUARTER); END TABLE FILE WF_RETAIL PRINT START_DATE QTRSTART AS 'Start,of Quarter' BY EMPLOYEE_NUMBER WHERE EMPLOYEE_NUMBER CONTAINS 'AH' ON TABLE SET PAGE NOPAGE END
The output is:
iWay Software |