SQL DATEADD() function adds a number to a specified date part of an input date and returns the modified date.
SYNTAX
DATEADD (datepart, value, inputdate )
date_part is the part of date to which the DATEADD() function will add the value.
value is an integer number to be added to the datepart of the inputdate.
Inputdate is a date that can be any date of type date , datetime , ..
Lets look at an example of SQL DATEADD() function.
To get a date by adding days in specific date
In following Statement, you can see a datepart argument is a day. So, the value will be added on a day part of a date.
Here, value 1 will add a one day in a date for first statement and value -3 will subtract three days from a date for second statement and returns modified dates accordingly.
select DATEADD (Day, 1,'2017-11-24 17:33:04.527') as DateAdd_Day select DATEADD(Day, - 3,'2017-11-24 17:33:04.527') as DateAdd_PreviousDay
To get a date by adding a year in a specific date
In following statement, a datepart argument is year. So, the value will be added on year part of date.
Here, value 1 will add one year in a date for first statement and -5 will subtract five years in a date for second statement , returns the modified date accordingly.
select DATEADD (YEAR, 1,'2017-11-24 17:33:04.527') as DateAdd_Year select DATEADD(YEAR, -5,'2017-11-24 17:33:04.527') as DateAdd_Year
To get a date by adding a month in a specific date
In following statement, a datepart argument is the month. So, the value will be added on the month part of date.
Here, value 2 will add two months in a date for first statement and -3 will subtract three months from date and returns the modified date accordingly.
select DATEADD (MONTH ,2 ,'2017-11-24 17:33:04.527') as DateAdd_Month select DATEADD(MONTH,- 3 ,'2017-11-24 17:33:04.527') as DateAdd_previousMonth
To get date by adding hours in a specific date
In following statement, a datepart argument is an hour. So the value will be added on an hour part of date.
Here, value 1 will add an hour in a date’s time for first statement and -3 hour will subtract a three hours from date’s time part and returns the modified date accordingly.
Remember, here we have passed a short abbreviation for date part hour that is HH in first statement that is also a valid date part in SQL.
select DATEADD(HH ,1 ,'2017-11-24 17:33:04.527') as DateAdd_Month select DATEADD(HOUR, - 3 ,'2017-11-24 17:33:04.527') as DateAdd_previousMonth
As you can see, In above SQL statement HH is taken for an hour date part, it means DATEADD() function also allows a short abbreviation for any date part like for a day = D or DD, month = M or MM, Year = Y or YY, week = wk or ww, hour = hh .
Following is the list of various date part which you can use with DATEADD() function as per your requirement.
day d, dd
month m, mm
year yy, yyyy
quarter qq, q
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
week wk, ww
dayofyear dy, y
Also Read..
