SQL Server Performance Forum – Threads Archive

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?

---

You could do that with a CASE expression.
< pseudocode >
CASE WHEN LEFT(column,3) IN (…) THEN column + ‘6’ ELSE column + ‘7’ END
< /pseudocode > —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

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.

---

[General Rule]
1 Use proper datatype DATETIME
2 Never use char, nchar, etc to store Date values
3 Do formation at front end
[/General Rule] I dont understand why VARCHAR or other CHAR datatypes are preferred over DATETIME datatype to store dates What is the need of inviting trouble in not using proper datatypes? Madhivanan Failing to plan is Planning to fail

---

Very good question in general! —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

Thanks Frank Now-a-days , seven out of Ten date related questions, it is known that DATETIME datatype is not used. Is it becuase that they want to store dates in the format they want? Madhivanan Failing to plan is Planning to fail

---

I know. There is a deep misunderstanding about the nature of the DATETIME data type. [<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

---

Its all conflict with local and server date formats Madhivanan Failing to plan is Planning to fail

---

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.

---

Yes. I know some newbies who prefer using varchar datatype to store dates just becuase avoid conflict with local and server. In India dmy is most commonly used. If the server is in mdy format the problem happens. The only way I can suggest is to express date values in ISO format ymd Madhivanan Failing to plan is Planning to fail

---

]]>