SQL Server Performance

Setting the last digit in the table column...

Discussion in 'SQL Server 2005 General Developer Questions' started by priya0123, May 3, 2007.

  1. priya0123 New Member

    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?
  2. FrankKalis Moderator

    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
  3. priya0123 New Member

    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.
    ?????
  4. Adriaan New Member

    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.
  5. priya0123 New Member

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

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

    WHERE LEN(DateUpdated) = 11
  7. priya0123 New Member

    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......
  8. Adriaan New Member

    What did your ALTER TABLE script look like?
  9. priya0123 New Member

    ALTER TABLE tblUser ALTER COLUMN DateUpdated nchar(12) NULL
  10. Adriaan New Member

    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
  11. priya0123 New Member

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

    ANy particular reason why you are using Nchar instead of char, or even varchar?
  13. priya0123 New Member

    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=':)' />
  14. MohammedU New Member

    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.
  15. Madhivanan Moderator

    quote:Originally posted by priya0123

    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......
    [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
  16. FrankKalis Moderator

    quote:
    What is the need of inviting trouble in not using proper datatypes?
    Very good question in general!

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  17. Madhivanan Moderator

    quote:Originally posted by FrankKalis


    quote:
    What is the need of inviting trouble in not using proper datatypes?
    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
  18. FrankKalis Moderator

    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>
  19. Madhivanan Moderator

    Its all conflict with local and server date formats

    Madhivanan

    Failing to plan is Planning to fail
  20. Adriaan New Member

    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.
  21. Madhivanan Moderator

    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

Share This Page