SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Q: For each * in table set date = date-100 years		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tiwas
Starting Member

Norway
37 Posts
Posted - 06/13/2012 :  06:04:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2111 Posts
Posted - 06/13/2012 :  06:36:28  Show Profile  Reply with Quote 
UPDATE
	y	
SET
	YourColumn = CASE  WHEN YourColumn > '1/1/2000' THEN DATEADD(yy, -1, YourColumn) END
FROM
	YourTable y










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
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3425 Posts
Posted - 06/13/2012 :  09:13:57  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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.
Edited by - Transact Charlie on 06/14/2012 11:54:49
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2111 Posts
Posted - 06/14/2012 :  11:23:25  Show Profile  Reply with Quote
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
Go to Top of Page

tiwas
Starting Member

Norway
37 Posts
Posted - 06/16/2012 :  14:28:23  Show Profile  Reply with Quote
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 :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 06/17/2012 :  12:03:06  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000