SQL Server - SqlDateTime overflow. - Asked By Lakshman Aditya on 02-Feb-10 07:07 PM

Hi,

I am getting the below weird error while selecting data in the table.

An error occurred while executing batch. Error message is: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

The query is as simple as - Select * from TABLE ; Not sure whats going on with the table in the database.
I cannot doubt the consistency of the table as that kind of values shouldn't be there on the table in the first place.

Please help.

Yes, use the DBCC CheckDB command with Data_Purity Option - [)ia6l0 iii replied to Lakshman Aditya on 02-Feb-10 09:29 PM

You would need to use the DBCC CheckDB command with the Data_Purity option to find the value and clen it. http://support.microsoft.com/kb/923247 at Microsoft Knowledge base article titled, " Troubleshooting DBCC error 2570 in SQL Server 2005" has all the required steps.

Thanks

Web Star replied to Lakshman Aditya on 02-Feb-10 09:47 PM

This is limitation of date range which is sotred in datetime column in sql server .

so this error comes means in your table there are some wrong date vlaue are inserted  so when u select than check these range in your select statemnet as

SELECT col1 FROM tablename
WHERE datecolumn < '1/1/1753 12:00:00 AM' OR datecolumn > '12/31/9999 11:59:59 PM'

Huggy Bear replied to Lakshman Aditya on 02-Feb-10 11:38 PM
one of your date field row in the database data is out of that range shown in the error message. As diablo suggested search through it and fix that value.
Lakshman Aditya replied to Huggy Bear on 03-Feb-10 01:49 PM
I picked the row causing the problem, the datetime fields have either the value within the range or NULL's
Now, I have updated them with the same values just to make sure, everything is fine.

When I do a select, it still gives the same error. Its driving me crazy :(
Lakshman Aditya replied to Lakshman Aditya on 03-Feb-10 01:56 PM
I even ran the DBCC CHECKTABLE with DATA_PURITY option. Its running for hours with no result.