CODEWITHSUNDEEP
sqlsql-serverdate
Rob
Rob

Reputation: 25

SQL Server 2008 R2: Updating past dates to the 'next' date

I'm using SQL Server 2008 R2. I have a table with lots of dates, some in the past, some in the future. What I would like to do would be to update them so the dates are the 'next' date, relative to today.

For example given the current date is '2014-02-09'

Current Value     Desired Value
-------------------------------------------------    
'2010-01-06'      '2015-01-06' (Updated to 2015)
'2008-03-28'      '2014-03-28' (Updated to 2014)

I'd need to account for dates on 29th of Feb, just to make this easier!

What is the best way to do this?

Upvotes: 1

Views: 152

Answers (3)

dnoeth
dnoeth

Reputation: 60502

untested:

CASE WHEN dateadd(yy, datecol, datediff(yy, datecol, getdate())) > getdate()
     THEN dateadd(yy, datecol, datediff(yy, datecol, getdate()))
     ELSE dateadd(yy, datecol, datediff(yy, datecol, getdate())+1)
END

This would be so much easier if SQL Server supported a simple age calculation :-)

dateadd(yy, datecol, age(datecol, getdate()) +1)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460298

This should work:

Declare @CurrentDate datetime
SET @CurrentDate = '2014-02-09'

;WITH NewDatesThisYear AS
(
    SELECT [Date], 
           NewDate = DateAdd(yy, YEAR(@CurrentDate)-1900,  
                     DateAdd(m,  MONTH([Date]) - 1, DAY([Date]) - 1)) 
    FROM dbo.Dates
)
, NewDates AS
(
    SELECT [Date], 
           NewDate = CASE WHEN NewDate < @CurrentDate 
                     THEN DateAdd(yy, 1,NewDate) 
                     ELSE NewDate END
    FROM NewDatesThisYear
)
UPDATE NewDates SET [Date] = NewDate
WHERE [Date] < @CurrentDate

Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271051

You can determine who many years in the past the date is. Then add that number of years plus one to the date:

update t
    set datecol = dateadd(year,
                          datediff(year, datecol, getdate()) + 1,
                          datecol)
    where datecol < getdate();

Upvotes: 1

Related Questions