Please disable your adblock and script blockers to view this page
 

Sql Server: Add or Subtract Days, Weeks, Months, Quarters and Years in/from Date

Introduction: In this article I am going to explain How to add or subtract days, weeks, months, quarters and years in/from any date in Sql Server.

In previous articles i explained How to Remove duplicate records/data from sql server table

Implementation: Let’s write queries to demonstrate the way.

 Add and subtract 2 days in current date

SELECT GETDATE() AS 'Current Date', DATEADD(DAY,-2,GETDATE()) AS 'Date before 2 days', DATEADD(DAY,2,GETDATE()) AS 'Date after 2 days'

Result:
Current Date
Date before 2 days
Date after 2 days
2016-08-18 22:01:33.103
2016-08-16 22:01:33.103
2016-08-20 22:01:33.103

Add and subtract 2 weeks in current date

SELECT GETDATE() AS 'Current Date', DATEADD(WEEK,-2,GETDATE()) AS 'Date before 2 weeks', DATEADD(WEEK,2,GETDATE()) AS 'Date after 2 weeks'

Result:
Current Date
Date before 2 weeks
Date after 2 weeks
2016-08-18 22:01:33.103
2016-08-04 22:01:33.103
2016-09-01 22:01:33.103

Add and subtract 2 months in current date

SELECT GETDATE() AS 'Current Date', DATEADD(MONTH,-2,GETDATE()) AS 'Date before 2 months', DATEADD(MONTH,2,GETDATE()) AS 'Date after 2 months'

Result:
Current Date
Date before 2 months
Date after 2 months
2016-08-18 22:01:33.103
2016-06-18 22:01:33.103
2016-10-18 22:01:33.103

Add and subtract 2 quarters in current date

SELECT GETDATE() AS 'Current Date', DATEADD(QUARTER,-2,GETDATE()) AS 'Date before 2 quarters', DATEADD(QUARTER,2,GETDATE()) AS 'Date after 2 quarters'

Result:
Current Date
Date before 2 quarters
Date after 2 quarters
2016-08-18 22:01:33.103
2016-02-18 22:01:33.103
2017-02-18 22:01:33.103

Add and subtract 2 years in current date

SELECT GETDATE() AS 'Current Date', DATEADD(YEAR,-2,GETDATE()) AS 'Date before 2 years', DATEADD(YEAR,2,GETDATE()) AS 'Date after 2 years'

Result:
Current Date
Date before 2 years
Date after 2 years
2016-08-18 22:01:33.103
2014-08-18 22:01:33.103
2018-08-18 22:01:33.103

 Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 
Previous
Next Post »

2 comments

Click here for comments
August 20, 2016 ×

Learnt about date logic from your post.Thanks keep it up.

Reply
avatar
September 09, 2016 ×

Thanks for your valuable feedback..stay connected and keep reading..

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon