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

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Updating a date field
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 10/29/2004 :  08:33:13  Show Profile  Visit AskSQLTeam's Homepage
Timothy writes "Hi,

I have to clean up a table with thousand of records that have erroneous dates. Specifically User entered the date, "2003-10-08" rather than 2004-10-08". What I need to do is upte the year from 2003 to 2004 leaving the month and day the same. Is there a way to accomplish this using the Update command?

All responses are most appreciated.

Thank you.


Flowing Fount of Yak Knowledge

2878 Posts

Posted - 10/29/2004 :  08:58:04  Show Profile  Visit Seventhnight's Homepage
well initially i would say

Update yourTable
Set wrongDate = '2004-10-08'
From yourTable
Where wrongDate = '2003-10-08'
and wrongDate = wrong

The part in bold should be replaced by something that identitifies that the record is indeed wrong. Are they entries with '2003-10-08' that are correct? Etc....

Go to Top of Page


United Kingdom
12543 Posts

Posted - 10/29/2004 :  09:24:50  Show Profile  Visit nr's Homepage
update tbl
set dte = dateadd(yy,1,dte)
where datepart(yy,dte) = 2003

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page


United Kingdom
22712 Posts

Posted - 10/29/2004 :  11:56:55  Show Profile
No leap year to worry about changing 2003 dates to 2004 - should there have been any records on 29th Feb 2004??

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000