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

and  Query to get first, last date and total number of days in month and Query to search any text in all stored procedures, views and functions and Query to get age in years, months and days from date of birth and Without primary key select / delete first or last n records 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. 

• Tweet
• Share
• Share
• Share

Related Post