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, which must provide a full component date.
Is the period whose starting or ending date you want to find. Can be one of the following:
By default, the first day of the week will be Sunday, but this can be changed using the WEEKFIRST parameter.
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 WFLITE QTRSTART/YYMD = DTRUNC(START_DATE, QUARTER); END TABLE FILE WFLITE 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:
The following request returns the date that is the start of the week for the start date of certain employees:
DEFINE FILE WFLITE DAY1/WT = DTRUNC(START_DATE, DAY); WKSTART/YYMD = DTRUNC(START_DATE, WEEK); DAY2/WT = DTRUNC(WKSTART, DAY); END TABLE FILE WFLITE PRINT START_DATE DAY1 AS 'DOW 1' WKSTART AS 'Start,of Week' DAY2 AS 'DOW 2' BY EMPLOYEE_NUMBER WHERE START_DATE GT '20130101' WHERE EMPLOYEE_NUMBER CONTAINS 'AH' ON TABLE SET PAGE NOPAGE END
The output is:
Employee Start Start Number Date DOW 1 of Week DOW 2 -------- ----- ----- ------- ----- AH118 2013/01/15 TUE 2013/01/13 SUN AH2272 2013/01/17 THU 2013/01/13 SUN AH288 2013/11/11 MON 2013/11/10 SUN AH3520 2013/09/23 MON 2013/09/22 SUN AH3591 2013/09/22 SUN 2013/09/22 SUN AH5177 2013/07/21 SUN 2013/07/21 SUN
The following request returns the dates that correspond to the first day of the week and the last day of the week for the given date.
DEFINE FILE WFLITE WEEKSTART/YYMD = DTRUNC(START_DATE, WEEK); WEEKEND/YYMD = DTRUNC(START_DATE, WEEK_END); END TABLE FILE WFLITE PRINT START_DATE WEEKSTART AS 'Start,of Week' WEEKEND AS 'End,of Week' BY EMPLOYEE_NUMBER WHERE EMPLOYEE_NUMBER CONTAINS 'AH1' ON TABLE SET PAGE NOPAGE END
The output is shown in the following image.