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.