Arithmetic overflow error converting expression to data type datetime!! - ERROR

Question

Arithmetic overflow error converting expression to data type datetime!! - ERROR

justin.fernandes007

Valued Member

Points: 58
More actions
July 29, 2011 at 12:50 pm

#242350

Hi,
I've been running a query to obtain sone data from a table "Tickets" which is as follows :
"SELECT *
FROM Tickets
WHERE CAST([Reported_Dtm] AS DATETIME )<='03/14/2011' AND CAST([Reported_Dtm] AS DATETIME )>='03/13/2011'"
This query was working fine for a while. However, it suddenly popped an error as follows :
"Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime."
Seems to be connected to the "CAST" expression used in the query.
Can anyone please help me with this?
Thanks
Justin

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323755 More actions · Answer 1

justin.fernandes007 (7/29/2011)
Hi,
I've been running a query to obtain sone data from a table "Tickets" which is as follows :
"SELECT *
FROM Tickets
WHERE CAST([Reported_Dtm] AS DATETIME )<='03/14/2011' AND CAST([Reported_Dtm] AS DATETIME )>='03/13/2011'"
This query was working fine for a while. However, it suddenly popped an error as follows :
"Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime."
Seems to be connected to the "CAST" expression used in the query.
Can anyone please help me with this?
Thanks
Justin

looks like your field ([Reported_Dtm] is a string isntead of a true datetime field....right?

try SELECT * FROM Tickets WHERE ISDATE(([Reported_Dtm]) = 0 and see how many rows of data are not convertable to a datetime filed...that might be a place to start.

the right thing would be to fix the datatype of the field if you can...i know sometimes that's not possible.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

justin.fernandes007 Valued Member Points: 58 More actions · Answer 2

Thanks Lowell...

However the database is on a server which I cant access. If i need data from this server,

I just get it using the Microsoft SQL server 2005 software remotely.

Can I change the data type using a query..? Also there are hundreds of records and I guess

using a query to change it's datatype would be pretty risky!?!?

Justin