November 13, 2006 at 7:09 am
I'm trying this simple solution to update the year and get an error. Will update not work this way?
update CUSTOMERS
set year(DATE) = '2000'
where year(DATE) = '1900'
Thanks.
November 13, 2006 at 8:08 am
You cannot assign a value to a function. Try something like:
UPDATE CUSTOMERS
SET [DATE] = DATEADD(year, 100, [DATE])
WHERE YEAR([DATE]) BETWEEN 1900 AND 1999
November 13, 2006 at 8:13 am
You didn't post the datatype of DATE column, but since you are using function YEAR(), I suppose it is datetime. Target of an update must be a pure column name; you can not use any function on this column. That is, you have to write SET date = ....., not SET YEAR(date).
Also, result of YEAR() is integer, so you should use number (2000, not '2000').
Datetime data contains year, month, day, and time. What you want to change is only a year... so the obvious chioce is DATEADD function.
UPDATE customers
SET [date] = DATEADD(year, 100, [date])
WHERE YEAR([date]) = 1900
or even better, to take advantage of indexes
UPDATE customers
SET [date] = DATEADD(year, 100, [date])
WHERE [date] >= '19000101' and [date] <'19010101'
If you have some problematic entries from later years, you can update them all with the same SQL, just increase the upper limit for date.
Depending on number of rows and requirements for continuous access to the data you could consider dividing the update into smaller chunks, to avoid long-time locking of the table.
BTW, "date" is reserved word and if you can't change column names in the database, you should at least use [] around the word "date", so that SQL Server knows it is a column name.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply