Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting the 'next' occurance of historic dates Expand / Collapse
Author
Message
Posted Sunday, February 09, 2014 2:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 09, 2014 4:15 PM
Points: 3, Visits: 3
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?
Post #1539592
Posted Sunday, February 09, 2014 2:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 1,059, Visits: 5,752
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?


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1539594
Posted Sunday, February 09, 2014 2:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 09, 2014 4:15 PM
Points: 3, Visits: 3
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.

Post #1539595
Posted Sunday, February 09, 2014 3:21 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 1,059, Visits: 5,752
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




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1539598
Posted Sunday, February 09, 2014 3:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 09, 2014 4:15 PM
Points: 3, Visits: 3
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"?
Post #1539600
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse