Arithmetic overflow[resolved]

[thebloke]

Here's one for you guys.

I had this sp.....

CREATE PROCEDURE spVisitors AS
select convert(varchar,(getdate()-(day(getdate())-1)),101) as startdate, convert(varchar,getdate(),101) as enddate,
sum([count]) as visits, sum(qcount) as requests, pid
from tblcounterv3
where (convert(varchar,cast(countdate as datetime),101) between convert(varchar,(getdate()-(day(getdate())-1)),101) 
and convert(varchar,getdate(),101))
group by pid
GO

which worked fine until I set the default language settings on my server from English US to English UK.

Now I get this error :

Server: Msg 8115, Level 16, State 2, Procedure spVisitors, Line 3
Arithmetic overflow error converting expression to data type datetime.


Any ideas?

[mendhak]

Try running that SP's code in query analyzer (minus the create procedure part) and print out what values you are getting for the getdates() and the convert(...,101). Perhaps the switch to the UK format is causing problems in the date time formatting.

[kaffenils]

The only conversion you do to a datetime data type is

cast(countdate as datetime)

. What format is the countdate column? Try executing this query

select cast(countdate as datetime) from tblcounterv3

Does it give you the same error?

[thebloke]

I did run it in Query Analyser and got the same result. I think the issue is that prior to me changing the settings, the values in the countdate column (which is nvarchar datatype) were being stored as 9/23/2005 however after the change they were going in as 23/09/2005.

I've tried to run

select cast(countdate as datetime) from tblcounterv3

and get the same error.....

however, I've just tried this

'using style 103 instead of 101
select convert(datetime,countdate,103) from tblcounterv3

and this returns the date values formatted as 2005-09-23 00:00:00.000

I tried changing all of the 101's in the original sp to 103's but still get the arithmetic overflow error

[kaffenils]

You only need to change the convert to datetime. Notce that I have replaced the CAST function with CONVERT.

CREATE PROCEDURE spVisitors AS
select convert(varchar,(getdate()-(day(getdate())-1)),101) as startdate, convert(varchar,getdate(),101) as enddate,
sum([count]) as visits, sum(qcount) as requests, pid
from tblcounterv3
where (convert(varchar,convert(datetime,countdate,103),101) between convert(varchar,(getdate()-(day(getdate())-1)),101) 
and convert(varchar,getdate(),101))
group by pid
GO

I really don't understand why you store dates in a varchar column. As you can see it can give you all sorts of problems.

[thebloke]

Sussed it:

select (getdate() - (day(getdate())-1)) as startdate, getdate() as enddate,
sum([count]) as visits, sum(qcount) as requests, pid
from tblcounterv3
where (convert(datetime,countdate,103) between (getdate() - (day(getdate())-1))
and getdate())
group by pid

Thanks guys

[kaffenils]

Sussed it:

select (getdate() - (day(getdate())-1)) as startdate, getdate() as enddate,
sum([count]) as visits, sum(qcount) as requests, pid
from tblcounterv3
where (convert(datetime,countdate,103) between (getdate() - (day(getdate())-1))
and getdate())
group by pid

Thanks guys

So you discovered yourself that you did a lot of uneccessary conversions back and forth between datetime and varchar. Have a nice weekend!

[thebloke]

Yeah, but they weren't unnecessary when I originally wrote the query, just after I changed the settings!