Setting the last digit in the table column...

I have a column nchar(11), which has data like
oct 01 200
nov 12 200
feb 12 200
may 01 200
dec 25 200
jan 12 200


so in all the rows of this columns I want to update the last digit

like
if oct, nov and dec then update the last to 6
else if jan , feb, march, april or may then 7

so that the output should look something like this
oct 01 2006
nov 12 2006
feb 12 2007
may 01 2007
dec 25 2006
jan 12 2007

how do I do it?

I wrote something like this

Update tblUser
set DateUpdated =
case when left(DateUpdated, 3) In ('Oct','Nov','Dec') then (DateUpdated + '6') else (DateUpdated + '7') end

I am getting this error

String or binary data would be truncated.
The statement has been terminated.
?????

Frank's solution was for presentation only, not for an update query. The column is defined as nchar(11), and you are trying to put in twelve characters.

Even I tried increasing the column size to 12 and more..but got the same error

Did you try adding a check that the string is not already 12 characters long ...

WHERE LEN(DateUpdated) = 11

1. First the column size was nchar(10)
2. I increased the size to nchar(12) using alter script
3. Then I tried to write the case statement like the one above using the where condition mentioned then it resulted in 0 rows
WHERE LEN(DateUpdated) = 12
4. then changed the where contion to
WHERE LEN(DateUpdated) = 10

then it resulted in 200 exisiting rows

why did my alter script did't change the column size, even though it executed with out errors......

What did your ALTER TABLE script look like?

ALTER TABLE tblUser ALTER COLUMN DateUpdated nchar(12) NULL

I was overlooking the exact data type - NCHAR. The LEN function will always return the defined length of a fixed-length data type, so you need to use DATALENGTH -

WHERE DATALENGTH(DateUpdated) = 10

It is showing the length as 24.
But even if I include the where statement, it is throwing the same error as before

ANy particular reason why you are using Nchar instead of char, or even varchar?

i just got the answer, for some reason it was storing blank spaces at the end, so i just used the trim()<br />and it wroked...<br />Thank u all for the replies <img src='/community/emoticons/emotion-1.gif' alt='' />

Not for some reason... because of NCHAR....

Unicode data type takes double space so that you were getting 24....


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

The conflict is not with the local and server formats in and by themselves. The problem is that developers, especially when based in the US, are not processing date values properly. And if they think they can avoid this by using nvarchar for datetime, then they really don't know what they're doing.

The underlying problem is that as long as your client app and the database are running on Windows with US settings, you don't need to do anything special as a developer. But to make the application behave also under non-US settings, your code has to enforce the US format for dates when handling them as string values - which you need to do anyway, because other data types tend to reflect language settings that are usually unacceptable in the back-end.

If you are programming in VB or ASP, you need to understand the Variant data type, which interprets date-like strings as dates according to the local settings, which can cause day/month swapping before the 13th of the month.