DATEADD – Add or subtract days to or from a date

This function adds or subtracts a number of days to or from a date.

DATEADD(<date>,<n>, [<informat>],[<outformat>])
Where:
<date>
The date from which you want to add or subtract days.
<n>
The number of days to be added. Use a negative integer to subtract.
<informat>
The format of the input date. The default is YYMMDD.
<outformat>
The format of the output date that is created. The default is the same format of the input date.
The format strings can be made up as follows:
YYYY
Year, in the complete format.
YY
Last two digits of the year.
CC
Century part of the year.
MM
Month
MMM
First three characters of the month, in English and upper-case format. For example, JAN for January.
Month_name
Complete name of the month. Valid only for the output date.
W
Numeric day of the week, starting from 1 for Monday.
WW
First two characters of the day, in English and upper-case format. For example, MO for Monday.
WWW
First three characters of the day, in English and upper-case format. For example, MON for Monday.
DD
Day.
Day_name
Complete name of the day of the week. Valid only for the output date.

Any other characters is considered literally in the date format. For example, the string DD/MM/YY generates a date like 31/10/21.

The following command returns 200229:
DATEADD(“200228”,”1”)
The following command returns 210301:
DATEADD(“210228”,”1”)
The following command returns 01/01/22:
DATEADD("25/12/21",7,"DD/MM/YY")
The following command returns 01-02-2022:
DATEADD("25/12/21",8,"DD/MM/YY","MM-DD-YYYY")
Consider that:
  • If there is no date part in the input format, but it is in the output format, they will be set to their format placeholder for each field. For example, MM.
  • The equal sign (=) can be used in place of a date to indicate the current date.
  • +n can be used in place of a date to indicate n days after the current date.
  • -n can be used in place of a date to indicate n days before the current date.
  • In all cases the relative dates must be contained in quotes for function calls.
  • To use textual months in another language, use the OPTIONS MMMM keyword to set months in an alternative language.
  • If the text version of a month or weekday does not match any known month or weekday, their format placeholder will be returned as question marks in the output. For example ??
  • If the month or weekday placeholders are in lower case in the output format, the month or day will be output in mixed case. For example, Mon.
  • The day of the week is always calculated from the date part of the input date, regardless of what day could be in the input string.