Author |
Topic |
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-01-28 : 15:36:34
|
I have a column with data type varchar(8)'yyyymmdd' format and when I use the code below I get error "Arithmetic overflow error converting expression to data type datetime"select cast(date as datetime) from tbltransactionwhere cast(date as datetime) = '20130101' However if I change the above code as below, it works fine.select cast(date as datetime) from tbltransactionwhere date = '20130101' May I know where I'm making mistakes? |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-28 : 15:44:32
|
Since the cast works in the select list, but not in the where clause, that is somewhat puzzling. Can you try the following and see if it returns any rows? I am not expecting any, but just want to cover that groundSELECT date FROM tbltransaction WHERE ISDATE(date) = 0; Another thing to try would be:SELECT date FROM tblTransction WHERE date = CAST('20130101' AS DATETIME); |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-01-28 : 15:47:58
|
One or more rows are not a valid date. When using CAST as datetime they fail to convert due to overflow. The 2nd version of your query does not use CAST and therefore doesn't encounter the problem, as it is comparing strings, not dates.If your data is supposed to be dates, use a proper data type for it (date, datetime, datetime2) instead of varchar, you'll avoid exactly this problem. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-28 : 15:51:54
|
I thought that too Rob, but in the second version, he is still casting the date to datetime in the select part of the query. I would have expected that to fail as well if the where clause failed when casting. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-01-28 : 16:23:21
|
It's not the CAST in the SELECT clause that's the issue, it's the CAST in the WHERE clause. That has to evaluate every row, even those that cannot be converted, while the original does not. Here's a repro:DECLARE @t TABLE(DATE VARCHAR(8))INSERT @t VALUES('99999999')INSERT @t VALUES('20130101')INSERT @t VALUES('20139901')SELECT CAST(DATE AS DATETIME) FROM @t WHERE DATE='20130101' -- works, comparing strings, casting to dateSELECT CAST(DATE AS DATETIME) FROM @t WHERE DATE<>'20130101' -- fails, comparing strings, can't convert datesSELECT CAST(DATE AS DATETIME) FROM @t WHERE CAST(DATE AS DATETIME)='20130101' -- fails, can't convert datesSELECT DATE FROM @t WHERE ISDATE(DATE)=0 -- failing rows |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-28 : 16:28:50
|
Yep. I see that. Thanks Rob. |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-01-29 : 02:54:54
|
Thank you - @robvolk and James@robvolkWhen I run your code "select date from table where isdate(date) = 0", I get output like "20121000", "20121000", "null", etc etc.What exactly isdate(date) = 0 does here?@JamesYour code cast('20130101' as datetime) gets me error "An expression of non-boolean type specified in a context where a condition is expected, near |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 03:28:44
|
quote: Originally posted by learning_grsql Thank you - @robvolk and James@robvolkWhen I run your code "select date from table where isdate(date) = 0", I get output like "20121000", "20121000", "null", etc etc.What exactly isdate(date) = 0 does here?@JamesYour code cast('20130101' as datetime) gets me error "An expression of non-boolean type specified in a context where a condition is expected, near
checks if value can be converted to a valid datetime value and if yes gives 1 else 0 as bit result------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|