SQL Server Performance

whats wrong with datetime ?

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

  1. priya0123 New Member

    select Name, address from tblUser
    where
    DateUpdated > isnull(DateValidated,'01/01/1900')

    When I run the query I got the following error

    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type datetime.

    what's wrong with it?
  2. ndinakar Member


    select Name, address from tblUser
    where
    DateUpdated > (case when DateValidated IS NULL Then '01/01/1900' ELSE DateValidated END)


    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. FrankKalis Moderator

    Are you sure DateUpdated and DateValidated are of data type DATETIME? You may also try ISNULL(DateValidated, CAST('01/01/1900' AS DATETIME)) or use the CAST in the WHERE clause Dinakar gave you.


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  4. khtan New Member

    try specify the date in ISO format YYYYMMDD '19000101'


    KH
  5. priya0123 New Member

    I found out whats wrong with it, as Frank mentioned, i am surprised to see that DateUpdated is nchar(10).
    So now I am trying to alter the column without deleting it from nchar to datetime.

    How do I do it? does it allow conversion from nchar to datetime? I am doing it as i have data in that column, which I dont want to loose....
  6. FrankKalis Moderator

    I would check prior to changing if all values are convertible to DATETIME. You could use the ISDATE() function for this. A result of 0 you mean that you have "bad data" that needs to be corrected before you could change. If the result is 1 in every row, an ALTER TABLE ... ALTER COLUMN... DATETIME should succeed.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  7. priya0123 New Member

    I tried that, I cannot alter the column to datetime......
  8. Madhivanan Moderator

    What is the use of having date values in nchar datatype?<br />You should always use proper datatype<br /><br />Create new column with datatime datatype<br />Other that with nchar values<br />Drop nchar column<br />Rename datetime column same as that of nchar column<br /><br />And if you want to format the dates, do it in front end [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  9. FrankKalis Moderator

    quote:Originally posted by priya0123

    I tried that, I cannot alter the column to datetime......
    What was your statement and what was the error message?

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

Share This Page