DTRUNC: Returning the Start of a Date Period for a Given Date

Syntax: How to Return the First or Last Date of a Date Period

DTRUNC(date_or_timestamp, date_period, extend)

where:

date_or_timestamp

Date or date-time

Is the date or timestamp of interest, which must provide a full component date.

date_period

Is the period whose starting or ending date you want to find. Can be one of the following:

DAY, returns the date that represents the input date (truncates the time portion, if there is one).
YEAR, returns the date of the first day of the year.
MONTH, returns the date of the first day of the month.
QUARTER, returns the date of the first day in the quarter.
WEEK, returns the date that represents the first date of the given week.
By default, the first day of the week will be Sunday, but this can be changed using the WEEKFIRST parameter.
YEAR_END, returns the last date of the year.
QUARTER_END, returns the last date of the quarter.
MONTH_END, returns the last date of the month.
WEEK_END, returns the last date of the week.

extend

Optional. Is a number that indicates how many of the specified date components to include in the resulting date period.

Since all intervals have to be the same size, the extend argument is limited to the following values for the date period:

YEAR. No limitations.
QUARTER. 1 and 2 only.
MONTH. 1, 2, 3, 4, and 6 only.
HOUR. 1, 2, 3, 4, 6, and 12 only.
MINUTE. 1, 2, 3, 4, 5, 6, 10, 15, 20, and 30 only.
SECOND. 1, 2, 3, 4, 5, 6, 10, 15, 20, and 30 only.

Example: Returning the First Date in a Date Period

In the following request, 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:

Example: Using the Start of Week Parameter for DTRUNC

The following request returns the date that is the start of the week for the start date of certain employees:

DEFINE FILE WF_RETAIL
DAY1/WT = DTRUNC(START_DATE, DAY); 
WKSTART/YYMD = DTRUNC(START_DATE, WEEK);
DAY2/WT = DTRUNC(WKSTART, DAY); 
END
TABLE FILE WF_RETAIL
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

Example: Returning the Date of the First and Last Days of a Week

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 WF_RETAIL
WEEKSTART/YYMD = DTRUNC(START_DATE, WEEK); 
WEEKEND/YYMD = DTRUNC(START_DATE, WEEK_END); 
END
TABLE FILE WF_RETAIL
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.

Example: Returning a Date Using the Extend Argument

In the following request, given the date of birth for each employee, the DTRUNC function uses the extend argument to return the start date of the decade in which they were born.

DEFINE FILE WF_RETAIL
BIRTH_DECADE/YYMD = DTRUNC(DATE_OF_BIRTH, YEAR, 10);
END
TABLE FILE WF_RETAIL
PRINT DATE_OF_BIRTH BIRTH_DECADE AS 'Start,of Decade'
BY EMPLOYEE_NUMBER
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.