SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 arithmetic overflow error converting expression
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 01/28/2013 :  15:36:34  Show Profile  Reply with Quote
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

3704 Posts

Posted - 01/28/2013 :  15:44:32  Show Profile  Reply with Quote
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);
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 01/28/2013 :  15:47:58  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 01/28/2013 :  15:51:54  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 01/28/2013 :  16:23:21  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 01/28/2013 :  16:28:50  Show Profile  Reply with Quote
Yep. I see that. Thanks Rob.
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 01/29/2013 :  02:54:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/29/2013 :  03:28:44  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000