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

Datetime:2016-08-23 02:27:18          Topic: SQL Server  SQL           Share

Introduction : In this article I am going to explain How to add or subtract d ays, 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.  





About List