DATEADD: Adding or Subtracting a Date Unit to or From a Date

How to:

Available Languages: reporting, Maintain

The DATEADD function adds a unit to or subtracts a unit from a date format. A unit is one of the following:

Do not use DATEADD with Dialogue Manager. DATEADD requires a date to be in date format; Dialogue Manager interprets a date as alphanumeric or numeric.

You add or subtract non day-based dates (for example, YM or YQ) directly without using DATEADD.

DATEADD works only with full component dates.

Top of page
x
Syntax: How to Add or Subtract a Date Unit to or From a Date 
DATEADD(date, 'unit', #units, outfield)

where:

date

Date

Is a full component date.

unit

Alphanumeric

Is one of the following enclosed in single quotation marks:

Y indicates a year unit.

M indicates a month unit.

D indicates a day unit.

WD indicates a weekday unit.

BD indicates a business day unit.

#units

Integer

Is the number of date units added to or subtracted from date. If this number is not a whole unit, it is rounded down to the next largest integer.

outfield

Date (numeric date offset)

Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks.

Example: Truncation With DATEADD

The number of units passed to DATEADD is always a whole unit. For example 

DATEADD(DATE, 'M', 1.999)

adds one month because the number of units is less than two.

Example: Using the Weekday Unit

If you use the weekday unit and a Saturday or Sunday is the input date, DATEADD changes the input date to Monday. The function 

DATEADD('910623', 'WD', 1)

in which DATE is either Saturday or Sunday yields Tuesday; Saturday and Sunday are not weekdays, so DATEADD begins with Monday and adds one.

Note that the single quotes around the number in the first argument, ‘910623’, causes it to be treated as a natural date literal.
Example: Adding Weekdays to a Date (Reporting)

DATEADD adds three weekdays to NEW_DATE. In some cases, it adds more than three days because HIRE_DATE_PLUS_THREE would otherwise be on a weekend. 

TABLE FILE EMPLOYEE
PRINT FIRST_NAME AND HIRE_DATE AND COMPUTE
NEW_DATE/YYMD = HIRE_DATE;
HIRE_DATE_PLUS_THREE/YYMD = DATEADD(NEW_DATE, 'WD', 3);
BY LAST_NAME
WHERE DEPARTMENT EQ 'MIS';
END

The output is:

LAST_NAME     FIRST_NAME  HIRE_DATE  NEW_DATE    HIRE_DATE_PLUS_THREE
---------     ----------  ---------  --------    --------------------
BLACKWOOD     ROSEMARIE    82/04/01  1982/04/01  1982/04/06
CROSS         BARBARA      81/11/02  1981/11/02  1981/11/05
GREENSPAN     MARY         82/04/01  1982/04/01  1982/04/06
JONES         DIANE        82/05/01  1982/05/01  1982/05/06
MCCOY         JOHN         81/07/01  1981/07/01  1981/07/06
SMITH         MARY         81/07/01  1981/07/01  1981/07/06
Example: Determining If a Date Is a Work Day (Reporting)

DATEADD determines which values in the TRANSDATE field do not represent work days by adding zero days to TRANSDATE using the business day unit. If TRANSDATE does not represent a business day, DATEADD returns the next business day to DATEX. TRANSDATE is then compared to DATEX, and the day of the week is printed for all dates that do not match between the two fields, resulting in a list of all non-work days. 

DEFINE FILE VIDEOTRK
DATEX/YMD  = DATEADD(TRANSDATE, 'BD', 0);
DATEINT/I8YYMD = DATECVT(TRANSDATE, 'YMD','I8YYMD');
END
TABLE FILE VIDEOTRK
SUM TRANSDATE NOPRINT
COMPUTE DAYNAME/A8 = DOWKL(DATEINT, DAYNAME); AS 'Day of Week'
BY TRANSDATE AS 'Date'
WHERE TRANSDATE NE DATEX
END

The output is:

Date      Day of Week 
----      ----------- 
91/06/22  SATURDAY
91/06/23  SUNDAY
91/06/30  SUNDAY
Example: Adding Months to a Date (Maintain)

DATEADD adds months to the DATE1 field: 

MAINTAIN
compute DATE1/yymd = '20000101'
compute DATE2/yymd=dateadd(date1, 'M', 2, date2);
type "DATE1 = <<DATE1 + 2 MONTHS =  DATE2 = <<DATE2"
END

The result is:

DATE1 = 2000/01/01+ 2 MONTHS =  DATE2 = 2000/03/01
WebFOCUS