Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > How to add/subtract days from a date – TSQL

How to add/subtract days from a date – TSQL


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

  1. el
    22.08.2013 02:12 | #1

    Thanks for the tutorial;

    By the way how to add date to 2 years, 2 months and the default day should be 1 altogether.
    for example : 2011-05-30 should be 2013-07-01

    Thank you.

  2. DW
    05.09.2013 05:19 | #2

    Try this
    select DATEADD(Day, -(DAY(DATEADD(Year, 2, DATEADD(Month, 2, getutcdate())))-1),DATEADD(Year, 2, DATEADD(Month, 2, getutcdate())))

  3. el
    10.09.2013 08:34 | #3

    Great It works.

    Thanks for the answer.

  4. Ritz
    13.09.2013 11:57 | #4

    Sir. Can you help me about in sql to know if
    Date_of_Residency=6months and 1 day past then Residency=’Transient’
    and if Date_of_Residency>=3years and 1 day then Residency=’Native’? I
    used datediff and dateadd but I cannot get the answer. tnx in advance

  5. Ante
    14.09.2013 04:31 | #5

    Maybe something like this? Create two variables that you can compare with:
    DECLARE @Date_of_Residency AS DATE = DATEADD(DAY, -1, GETDATE())
    DECLARE @Residency AS VARCHAR(10)

    DECLARE @Date_of_Residency_temp6_1 AS DATE = DATEADD(DAY, – DATEDIFF(DAY, DATEADD(MONTH, -6, GETDATE()), GETDATE()) – 1, GETDATE())
    DECLARE @Date_of_Residency_temp3_1 AS DATE = DATEADD(DAY, – DATEDIFF(DAY, DATEADD(YEAR, -3, GETDATE()), GETDATE()) – 1, GETDATE())

    if (@Date_of_Residency = @Date_of_Residency_temp6_1) SET @Residency = ‘Transient’
    else if (@Date_of_Residency <= @Date_of_Residency_temp3_1) SET @Residency = 'Native'

    SELECT @Residency

  6. janwane
    24.09.2013 07:38 | #6

    I need to know how to do this GETDATE() – 10 where “10” is a variable instead of a literal; i.e. GETDATE() – @DaysBack. Unfortunately this doesn’t work. I’ve spent quite a bit of time in research but all examples show the subtracted amount as a literal. Thank you.

  7. SS
    30.07.2014 12:05 | #7

    Thanks!! It z really Very Helpfull ?

  8. mayur
    19.03.2015 06:01 | #8

    how to send reminder 10 day before 1 year completion your package is going on expired like how to write query in sql server

  9. kj
    09.04.2015 08:12 | #9

    you could do it with dynamic sql, thus inserting the @DaysBack in a string, and then executing the String.

  1. No trackbacks yet.