Add and Subtract Dates,Weeks, Months, Year From Date using DATEADD in SQL Server

---

There are several functions available in SQL Server to work with Date manipulation.
We can use the DATEADD () function to Subtract number of days from DateTime in SQL Server.
The DATEADD () function allows you to add or subtract the specified number of units of time to a specified date/time value. In below example going to shows how we can subtract two date from the current DateTime in SQL server .

---

SQL Server DATEADD() Function Syntax:-

DATEADD (date/time_part , number , date )

Where date/time_part is the part of the date you want to be increased or decreased, number is the amount to increase or decrease datepart by, and date is the date to which the addition will take place.

SELECT DATEADD(DAY,10, '5/4/2018')
SELECT DATEADD(MONTH, -10, '5/4/2018')

OutPut:-

---

How to ADD Days from DateTime in SQL Server?

Addition of days Example:-

SELECT GETDATE() 'Current_Date', 
           DATEADD(day,4,GETDATE()) 'Current_Date + 4 Days'
SELECT GETDATE() 'Current_Date', 
           DATEADD(dd,10,GETDATE()) 'Current_Date + 10 Days'
SELECT GETDATE() 'Current_Date', 
           DATEADD(d,20,GETDATE()) 'Current_Date + 20 Days'

Output:-

---

Example:-

How to Subtract Days from DateTime in SQL Server?

SELECT GETDATE() 'Current_Date', 
           DATEADD(day,-4,GETDATE()) 'Current_Date - 4 Days'
SELECT GETDATE() 'Current_Date', 
           DATEADD(dd,-10,GETDATE()) 'Current_Date - 10 Days'
SELECT GETDATE() 'Current_Date', 
           DATEADD(d,-20,GETDATE()) 'Current_Date - 20 Days'

Output:-

 

---

Example :- Add And Subtract Weeks from DateTime in SQL Server

SELECT GETDATE() 'Current_Date',
 
           DATEADD(week,-4,GETDATE()) 'Current_Date - 4 week'
SELECT GETDATE() 'Current_Date', 
           DATEADD(wk,10,GETDATE()) 'Current_Date + 10 week'
           
           
SELECT GETDATE() 'Current_Date',
 
           DATEADD(ww,-20,GETDATE()) 'Current_Date - 20 week'

Output:-

---

How to Add And Subtract Months from DateTime in Sql Server

SELECT GETDATE() 'Current_Date', 
           DATEADD(month,4,GETDATE()) 'Current_Date + 4 Month'
SELECT GETDATE() 'Current_Date', 
           DATEADD(MM,-10,GETDATE()) 'Current_Date - 10 Month'
SELECT GETDATE() 'Current_Date', 
           DATEADD(M,-20,GETDATE()) 'Current_Date - 20 Month'

Output:-

---

How to Add And Subtract Quarters from DateTime in SQL Server

SELECT GETDATE() 'Current_Date',
 
           DATEADD(QUARTER,1,GETDATE()) 'Current_Date + 1 QUARTER'
SELECT GETDATE() 'Current_Date', 
           DATEADD(QQ,-3,GETDATE()) 'Current_Date - 3 QUARTER'
SELECT GETDATE() 'Current_Date',
 
           DATEADD(Q,-4,GETDATE()) 'Current_Date - 4 QUARTER'

Output:-

---

How to Add And Subtract Years from DateTime in SQL Server:-

SELECT GETDATE() 'Current_Date', 
           DATEADD(YEAR,5,GETDATE()) 'Current_Date + 1 YEAR'
SELECT GETDATE() 'Current_Date', 
           DATEADD(YY,-13,GETDATE()) 'Current_Date - 3 YEAR'
SELECT GETDATE() 'Current_Date', 
           DATEADD(YYYY,-40,GETDATE()) 'Current_Date - 4 YEAR'

Output:-

---