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
 Old Forums
 CLOSED - General SQL Server
 Updating a date field

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-10-29 : 08:33:13
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.

Timothy"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-29 : 08:58:04
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....



Corey
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-29 : 09:24:50
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

Kristen
Test

22859 Posts

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

Kristen
Go to Top of Page
   

- Advertisement -