SQL Server - 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.

Asked By Tammy Carson on 28-Apr-09 06:35 PM
We get this error when running queries on our sql 2005 server when we move our timezone ahead.  we require the ability to move dates ahead for testing purposes.  if we run the same query in our current timezone it works fine, when we implement a timezone in the future we get the error above.  Has anyone encountered this type of issue before and know how to resolve it?  or if there is a different method of moving the dates on our testing system ahead?  We can't just move it ahead as our network uses activedir and it won't allow the dates to be out of synch.

Sql Server only understands dates in a range. - Peter Bromberg replied to Tammy Carson on 28-Apr-09 09:18 PM

Less than January 1, 1753 is not permitted in SQL Server. SQL Server rejects all values that do not fall within the range from 1753 to 9999.

I suspect somehow you are passing a DateTime beyond that range. For example, the .NET DateTime.MinValue is way lower than 1753.

Range of value accept - Ravenet Rasaiyah replied to Tammy Carson on 28-Apr-09 10:02 PM

Hi

I'm wondering you are passing value not fall in supporting range of the date in sql server, here summary table .net and Sql server support date range.


Min DateMax Date
.Net0001-01-01 00:00:00.0009999-12-31 23:59:59.999
SQL Server1753-01-01 00:00:00.0009999-12-31 23:59:59.997


Please check you value fall within above range when you are pass to sql server.

thank you
Santhosh N replied to Tammy Carson on 28-Apr-09 10:30 PM
As theerror msg clearly says, thedates must be in the range 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM, I just dont think you ever need to test the date above this, then still you need to do that, I am afraid you need to switch your date column to some varchar type...