Tag Archives: DATEADD

How to Subtract Days, Weeks, Months, Quarters or Years from Date in 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:

How to add Hours, Minutes, Seconds to a DateTime in Sql Server

In this article we will discuss on How to add Hours, Minutes, Seconds to a DateTime in Sql Server?

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

How to add Hours to DateTime in Sql Server?

We can use DATEADD() function like below to add hours to DateTime in Sql Server. DATEADD() functions first parameter value can be hour or hh all will return the same result. Below example shows how we can add two hours to Current DateTime in Sql Server:

SELECT GETDATE() 'Now',
           DATEADD(hour,2,GETDATE()) 'Now + 2 Hours'
SELECT GETDATE() 'Now',
           DATEADD(hh,2,GETDATE()) 'Now + 2 Hours'

RESULT:
Add Hours to DateTime in Sql Server

[ALSO READ] How to get difference between two dates in Years, Months and days

How to add Minutes to DateTime in Sql Server?

We can use DATEADD() function like below to add minutes to DateTime in Sql Server. DATEADD() functions first parameter value can be minute or mi or n all will return the same result. Below example shows how we can add two minutes to Current DateTime in Sql Server:

SELECT GETDATE() 'Now',
           DATEADD(minute,2,GETDATE()) 'Now + 2 Minutes'
SELECT GETDATE() 'Now',
           DATEADD(mi,2,GETDATE()) 'Now + 2 Minutes'
SELECT GETDATE() 'Now',
           DATEADD(n,2,GETDATE()) 'Now + 2 Minutes'

RESULT:
Add Minutes to DateTime in Sql Server 1

How to add Seconds to DateTime in Sql Server?

We can use DATEADD() function like below to add seconds to DateTime in Sql Server. DATEADD() functions first parameter value can be second or ss or s all will return the same result. Below example shows how we can add two seconds to Current DateTime in Sql Server:

SELECT GETDATE() 'Now',
           DATEADD(second,2,GETDATE()) 'Now + 2 Seconds'
SELECT GETDATE() 'Now',
           DATEADD(ss,2,GETDATE()) 'Now + 2 Seconds'
SELECT GETDATE() 'Now',
           DATEADD(s,2,GETDATE()) 'Now + 2 Seconds'

RESULT:
Add Seconds to DateTime in Sql Server

[ALSO READ] How to get difference between two dates in Years, Months and days

How to add Days, Weeks, Months, Quarters or Years to a Date in Sql Server

In this article we will discuss on How to add Days, Weeks, Months, Quarters or Years to a Date in Sql Server?

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

How to add Days to DateTime in Sql Server?

We can use DATEADD() function like below to add days to 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 add two days to 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:
Add days to DateTime in Sql Server

Alternatively, we can add Days to Date like below in Sql Server. In the below example we are add adding 2 days to a DateTime.

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

RESULT:
Add days to DateTime in Sql Server 1

How to add Weeks to DateTime in Sql Server?

We can use DATEADD() function to add 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 add two weeks to 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:
Add Weeks to DateTime in Sql Server 1

How to add Months to DateTime in Sql Server?

We can use DATEADD() function like below to add Months to 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 add two months to 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:
Add Months to DateTime in Sql Server

How to add Quarters to DateTime in Sql Server?

We can use DATEADD() function like below to add Quarters to 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 add two months to 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:
Add Quarters to DateTime in Sql Server

How to add Years to DateTime in Sql Server?

We can use DATEADD() function like below to add Years to 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 add two Years to 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:
Add Years to DateTime in Sql Server

Also Read:

Sql Server’s DATEADD function may return different result in a scenario where we are adding one month at a time from adding multiple months at a time to the date.

Today, I came across a scenario were observed that DATEADD function is returning different result if we add 1 month at time from adding multiple months at a time. At the first instance results were surprising to me, later found it to be logically correct. May be for most of you these  results were obvious, but at first look it surprised me 🙂

Let us understand this with an example:

Declare @ExDate DATE = '2012-01-31'
SELECT @ExDate AS ExDate,
DATEADD(MONTH,2,@ExDate) AS AddTwoMonths,
DATEADD(MONTH,1,DATEADD(MONTH,1,@ExDate)) AddOneMonthThenOneMoreMonth

RESULT:

ExDate     AddTwoMonths AddOneMonthThenOneMoreMonth
---------- ------------ ---------------------------
2012-01-31 2012-03-31   2012-03-29
(1 row(s) affected)

In the above example I was expecting DATEADD(MONTH,2,@ExDate) same as DATEADD(MONTH,1,DATEADD(MONTH,1,@ExDate)), as at the end of the day we are adding two months in both the cases. Finally, after seeing results of this example we can conclude that adding one month shouldn’t go from Jan to March or shouldn’t bring a non existing date in  Feb i.e. 31st Feb.

This behaviour is not specific to Sql Server, same behaviour can be observed in C# too.

Please correct me, if my understanding is wrong. Comments are always welcome.

Note: All the examples in this article are tested on Sql Server 2008 version. And in this article using the new Date data type introduced in Sql Server 2008.