arithmetic overflow error converting expression

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

arithmetic overflow error converting expression

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 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?

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 ground

SELECT 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 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
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

@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

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

@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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources