Q: For each * in table set date = date-100 years

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)

Q: For each * in table set date = date-100 years

Author

Topic

tiwas
Starting Member

37 Posts

Posted - 2012-06-13 : 06:04:20

Could someone help me do this? I imported a db from an old system, and it left me with all dates for all entries in my tables 100 years off. Basically, I just need to check every record if the date is on or later than 01.01.2000 and if so set it to <date-100 years>.

Thanks!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-13 : 06:36:28

[code]UPDATE
y
SET
YourColumn = CASE WHEN YourColumn > '1/1/2000' THEN DATEADD(yy, -1, YourColumn) END
FROM
YourTable y[/code]

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-13 : 09:13:57

WARNING WILL ROBINSON. (Don that is some pretty terrible code)

That update can leave you with unexpected NULLS.

example:


BEGIN TRAN

DECLARE @foo TABLE (
	[val] CHAR(1)
	)
	
INSERT @foo VALUES ('A'),('B')

SELECT * FROM @foo

UPDATE f SET
	[val] = CASE WHEN [val] < 'B' THEN 'Z' END
FROM
	@foo AS f

SELECT * FROM @foo

ROLLBACK

with results:


val
----
Z
NULL

Also -- don't compare dates with that kind of string!

Also -- OP wanted -100 years, not -1 years.

Probably safer is:


UPDATE
	y	
SET
	YourColumn = DATEADD(yy, -100, YourColumn)
FROM
	YourTable AS y
WHERE
        YourColumn  >= '20000101'

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-14 : 11:23:25

Doh. i pasted the wrong code. And i KNOW its terrible. He wanted something terrible done

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

tiwas
Starting Member

37 Posts

Posted - 2012-06-16 : 14:28:23

quote:

Originally posted by DonAtWork

UPDATE
	y	
SET
	YourColumn = CASE  WHEN YourColumn > '1/1/2000' THEN DATEADD(yy, -1, YourColumn) END
FROM
	YourTable y

Thanks :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 12:03:06

quote:
Originally posted by tiwas

quote:
Originally posted by DonAtWork

UPDATE
y
SET
YourColumn = CASE WHEN YourColumn > '1/1/2000' THEN DATEADD(yy, -1, YourColumn) END
FROM
YourTable y

Thanks :)

Hope you saw Charlie's comment too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources