Getting the 'next' occurance of historic dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Getting the 'next' occurance of historic dates

Author

Topic

Rob5846
Starting Member

2 Posts

Posted - 2014-02-09 : 16:31:28

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' 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?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-09 : 20:52:59

use DATEADD( year , <no of years to add>, date_column )

How do you want to handle Feb 29 if the new date does not have Feb 29 ?

KH
[spoiler]Time is always against us[/spoiler]

Rob5846
Starting Member

2 Posts

Posted - 2014-02-10 : 02:19:47

Hi, I've used DateAdd before, but isn't enough alone to get my result.
The table has thousands of dates, so I'm looking to automatically calculate the number of years to add.

For example, not all dates in 2013 are the same, some will be 1, others 2 years.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-10 : 03:53:06

quote:
For example, not all dates in 2013 are the same, some will be 1, others 2 years.

so what is the rule ?

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-10 : 13:47:59

You question is not clear. Here are some links that can help you prepare your question along with sample data and expected output so we can help you better:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-02-10 : 16:41:12

[code]

UPDATE tn
SET date_column = DATEADD(YEAR, DATEDIFF(YEAR, date_column, GETDATE()) +
CASE WHEN CONVERT(char(5), date_column, 101) >= CONVERT(char(5), GETDATE(), 101) THEN 0 ELSE 1 END, date_column) AS date_column_New
FROM dbo.tablename tn
--WHERE ...

--For example:
SELECT
date_column, DATEADD(YEAR, DATEDIFF(YEAR, date_column, GETDATE()) +
CASE WHEN CONVERT(char(5), date_column, 101) >= CONVERT(char(5), GETDATE(), 101) THEN 0 ELSE 1 END, date_column) AS date_column_New
FROM (
SELECT CAST('20100106' AS date) AS date_column UNION ALL --'**2015**-01-06' (Updated to 2015)
SELECT '20080328' -- '**2014**-03-28' (Updated to 2014)
) AS test_dates

[/code]

As to Feb 29th, since some years simply do not have that date, you will need a separate bit (or byte) flag that indicates Feb 29. Otherwise, when Feb 29th gets converted to Feb 28 or Mar 1 for a different year, you will have no way to know that the original date was Feb 29.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources