Archive

Posts Tagged ‘getdate’

How to add/subtract days from a date – TSQL

20.05.2011 9 comments

To add or subtract days from a date, you can simply use:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      GETDATE() AS 'Today',

            GETDATE() + 10 AS '10 Days Later',

            GETDATE() 10 AS '10 Days Earlier'

Result Set:

Today                   10 Days Later           10 Days Earlier

———————– ———————– ———————–

2011-05-20 21:04:24.527 2011-05-30 21:04:24.527 2011-05-10 21:04:24.527

 

(1 row(s) affected)

 

 

However, if you need to add months/years to date, you need to use DATEADD() function.

 

Syntax: DATEADD( datepart, number, date)

Where,  datepart = year, quarter, month, day… (Check BOL for full list)

              number = is a integer values that is to be added to the “datepart” of date

              date = date

 

It can be used as:

 

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      GETDATE(), 'Today'

UNION ALL

SELECT      DATEADD(DAY,  10, GETDATE()), '10 Days Later'

UNION ALL

SELECT      DATEADD(DAY, 10, GETDATE()), '10 Days Earlier'

UNION ALL

SELECT      DATEADD(MONTH,  1, GETDATE()), 'Next Month'

UNION ALL

SELECT      DATEADD(MONTH, 1, GETDATE()), 'Previous Month'

UNION ALL

SELECT      DATEADD(YEAR,  1, GETDATE()), 'Next Year'

UNION ALL

SELECT      DATEADD(YEAR, 1, GETDATE()), 'Previous Year'

Result Set:

———————– —————

2011-05-20 21:11:42.390 Today

2011-05-30 21:11:42.390 10 Days Later

2011-05-10 21:11:42.390 10 Days Earlier

2011-06-20 21:11:42.390 Next Month

2011-04-20 21:11:42.390 Previous Month

2012-05-20 21:11:42.390 Next Year

2010-05-20 21:11:42.390 Previous Year

 

(7 row(s) affected)

 

Hope This Helps!

Vishal