SQL Server Performance Forum – Threads Archive

whats wrong with datetime ?

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?

---

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/

---

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

---

try specify the date in ISO format YYYYMMDD ‘19000101’
KH

---

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….

---

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

---

I tried that, I cannot alter the column to datetime……

---

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

---

What was your statement and what was the error message? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

]]>