How to Subtract Days, Weeks, Months, Quarters or Years from Date in Sql Server

Sql Server, , , , , , , , , , ,

In this article we will discuss on How to Subtract Days, Weeks, Months, Quarters or Years from DateTime in Sql Server?

You may also like to read the following other popular articles on Date and Time in Sql Server:

How to Subtract Days from DateTime in Sql Server?

We can use DATEADD() function like below to Subtract days from DateTime in Sql Server. DATEADD() functions first parameter value can be day or dd or d all will return the same result. Below example shows how we can subtract two days from Current DateTime in Sql Server:

SELECT GETDATE() 'Today', 
           DATEADD(day,-2,GETDATE()) 'Today - 2 Days'
SELECT GETDATE() 'Today', 
           DATEADD(dd,-2,GETDATE()) 'Today - 2 Days'
SELECT GETDATE() 'Today', 
           DATEADD(d,-2,GETDATE()) 'Today - 2 Days'

RESULT:
Subtract days from Datetime in Sql Server

Alternatively, we can Subtract Days from Datetime like below in Sql Server. In the below example we are subtracting 2 days from DateTime.

SELECT GETDATE() 'Today', GETDATE() - 2 'Today - 2 Days'

RESULT:
Subtract days from Datetime in Sql Server 1

How to Subtract Weeks from DateTime in Sql Server?

We can use DATEADD() function to Subtract weeks to DateTime in Sql Server. DATEADD() functions first parameter value can be week or wk or ww, all will return the same result. Below example shows how we can Subtract two weeks from Current DateTime in Sql Server:

SELECT GETDATE() 'Today',
           DATEADD(week,-2,GETDATE()) 'Today - 2 Weeks'
SELECT GETDATE() 'Today',
           DATEADD(wk,-2,GETDATE()) 'Today - 2 weeks'
SELECT GETDATE() 'Today',
           DATEADD(ww,-2,GETDATE()) 'Today - 2 Weeks'

RESULT:
Subtract weeks from Datetime in Sql Server

How to Subtract Months from DateTime in Sql Server?

We can use DATEADD() function like below to Subtract Months from DateTime in Sql Server. DATEADD() functions first parameter value can be month or mm or m, all will return the same result. Below example shows how we can Subtract two months from Current DateTime in Sql Server:

SELECT GETDATE() 'Today', 
           DATEADD(month,-2,GETDATE()) 'Today - 2 Months'
SELECT GETDATE() 'Today',
           DATEADD(mm,-2,GETDATE()) 'Today - 2 Months'
SELECT GETDATE() 'Today',
           DATEADD(m,-2,GETDATE()) 'Today - 2 Months'

RESULT:
Subtract months from Datetime in Sql Server

How to Subtract Quarters from DateTime in Sql Server?

We can use DATEADD() function like below to Subtract Quarters from DateTime in Sql Server. DATEADD() functions first parameter value can be quarter or qq or q, all will return the same result. Below example shows how we can Subtract two Quarters from Current DateTime in Sql Server:

SELECT GETDATE() 'Today',
         DATEADD(quarter,-2,GETDATE()) 'Today - 2 Quarters'
SELECT GETDATE() 'Today',
         DATEADD(qq,-2,GETDATE()) 'Today - 2 Quarters'
SELECT GETDATE() 'Today',
         DATEADD(q,-2,GETDATE()) 'Today - 2 Quarters'

RESULT:
Subtract Quarters from Datetime in Sql Server

How to Subtract Years from DateTime in Sql Server?

We can use DATEADD() function like below to Subtract Years from DateTime in Sql Server. DATEADD() functions first parameter value can be year or yyyy or yy, all will return the same result. Below example shows how we can Subtract two Years from Current DateTime in Sql Server:

SELECT GETDATE() 'Today',
           DATEADD(year,-2,GETDATE()) 'Today - 2 Years'
SELECT GETDATE() 'Today',
           DATEADD(yyyy,-2,GETDATE()) 'Today - 2 Years'
SELECT GETDATE() 'Today',
           DATEADD(yy,-2,GETDATE()) 'Today - 2 Years'

RESULT:
Subtract Years from Datetime in Sql Server

Also Read:

Leave a Reply

Your email address will not be published. Required fields are marked *