SQL DATEADD()

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 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 in a date for second statement and returns modified date 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 year 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 in a 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

Recommended for you

SQL Server DATEPART()

 1,094 total views,  3 views today