| Author |
 Topic  |
|
|
learning_grsql
Posting Yak Master
155 Posts |
 Posted - 01/28/2013 : 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 tbltransaction
where cast(date as datetime) = '20130101'
However if I change the above code as below, it works fine.
select cast(date as datetime) from tbltransaction
where date = '20130101'
May I know where I'm making mistakes? |
Edited by - learning_grsql on 01/28/2013 15:37:55
|
|
|
James K
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 01/28/2013 : 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
USA
15557 Posts |
Posted - 01/28/2013 : 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
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 01/28/2013 : 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
USA
15557 Posts |
Posted - 01/28/2013 : 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 date
SELECT CAST(DATE AS DATETIME) FROM @t WHERE DATE<>'20130101' -- fails, comparing strings, can't convert dates
SELECT CAST(DATE AS DATETIME) FROM @t WHERE CAST(DATE AS DATETIME)='20130101' -- fails, can't convert dates
SELECT DATE FROM @t WHERE ISDATE(DATE)=0 -- failing rows |
|
|
|
James K
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 01/28/2013 : 16:28:50
|
| Yep. I see that. Thanks Rob. |
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 01/29/2013 : 02:54:54
|
Thank you - @robvolk and James
@robvolk
When 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?
@James
Your code cast('20130101' as datetime) gets me error "An expression of non-boolean type specified in a context where a condition is expected, near |
Edited by - learning_grsql on 01/29/2013 02:59:34 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/29/2013 : 03:28:44
|
quote:
Originally posted by learning_grsql
Thank you - @robvolk and James
@robvolk
When 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?
@James
Your 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 MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
arithmetic overflow error converting expression
