Getting the 'next' occurance of historic dates

Question

Getting the 'next' occurance of historic dates

SQLQuestion 35902

Grasshopper

Points: 15
More actions
February 9, 2014 at 2:36 pm

#285332

Hi All,
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' occurance, 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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

This is straightforward using DATEDIFF and DATEADD. what do you want to do with a date which is exactly a year ago (or two years, or three)? Today one year ago becomes what?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

SQLQuestion 35902 Grasshopper Points: 15 More actions · Answer 2

Never consiered 'exact years' ago. Assuming today is '2014-02-09' and the date in the table is '2010-02-09', I would want this updating to today ('2014-02-09'). If I ran it tomorrow, it would be updated to '2015-02-09'

In Summary, 'exact years' ago would be updated to today.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Here's a little test harness. If you can't figure it out from here, let us know:

WITH

Today AS (SELECT Today = '20120228' UNION ALL SELECT '20120229' UNION ALL SELECT CAST(GETDATE() AS DATE) UNION ALL SELECT '20160228' UNION ALL SELECT '20160229' UNION ALL SELECT '20160301'),

TableWithDatesInIt AS (SELECT TableDate = '20120228' UNION ALL SELECT '20120229' UNION ALL SELECT CAST(GETDATE() AS DATE))

SELECT *,

YearsDiff = DATEDIFF(year,TableDate,Today),

CASE WHEN NearlyThere < Today THEN DATEADD(year,1,NearlyThere) ELSE NearlyThere END

FROM TableWithDatesInIt d

CROSS JOIN Today t

CROSS APPLY (SELECT NearlyThere = DATEADD(year,DATEDIFF(year,TableDate,Today),TableDate)) x

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

SQLQuestion 35902 Grasshopper Points: 15 More actions · Answer 4

Thanks so far. I can't seem to get your example to work using the table I am using.

Can you advise how to amend it to use the sample table called "TableName", a date field called "DateField", and to output two columns, "OriginalDate" and "NextDate"?