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

pinkon5k 于 2023-06-21 发布在 SQL Server

关注(0)|答案(3)|浏览(174)

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?

sql-server

来源：https://stackoverflow.com/questions/21665177/sql-server-2008-r2-updating-past-dates-to-the-next-date

3条答案

按热度按时间

35g0bw711#

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();

赞(0）回复(0）举报 2023-06-21

brqmpdu12#

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

赞(0）回复(0）举报 2023-06-21

c8ib6hqw3#

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)

赞(0）回复(0）举报 2023-06-21

我来回答

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

3条答案

相关问题

热门标签

最新问答