SQL Server 2008 Datetime field overflow

Question

I am using C# to interface with a SQL database. the database has a DateTime field. When I try to write a DateTime object from C#, I get the following error:

ERROR [22008] [Microsoft][ODBC SQL Server Driver]Datetime field overflow

I found this on the topic:

http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/ac1b5a6d-5e64-4603-9c92-b75ba4e51bf2/

Is there any manipulation I can do to my DateTime object on the C# side?

EDITS I am trying to add DateTime.MinValue

Pls post the code you're using – OMG Ponies Jul 26 '10 at 20:21 — OMG Ponies, Jul 26 '10 at 20:21

score 5 · Accepted Answer · answered Jul 26 '10 at 20:22

5

Sounds like you are just passing in a bad date. For example, DateTime.MinValue is outside the accepted range for SQL Server datetime values by about 1600 years.

By the way, you shouldn't be using ODBC for C# to SQL Server communication. Using System.Data.SqlClient will give you much better performance.

answered Jul 26 '10 at 20:22

Jonathan Allen

68,373
70
259
447

wow, this was exactly the problem. I was trying to put in DateTime.MinValue. i bow in respect. – sbenderli Jul 26 '10 at 20:26

score 4 · Answer 2 · answered Jul 26 '10 at 20:26

4

The .NET DateTime object has a bigger range than the SQL date/time field.

In your data access layer, anytime your writing a date to the database, ensure that is inside the rate of SqlDateTime.MinValue and SqlDateTime.MaxValue.

answered Jul 26 '10 at 20:26

Mike Mooney

11,729
3
36
42

score 2 · Answer 3 · edited Dec 12 '11 at 19:21

2

I'm quiet sure, your .NET DateTime is not initialized, what means it is "0000-01-01" and this is not a valid value for SQL Server DATETIME and often not a desired value ;-)

edited Dec 12 '11 at 19:21

answered Jul 26 '10 at 20:25

Florian Reischl

3,788
1
24
19

score 1 · Answer 4 · answered Apr 05 '19 at 05:54

Change all your datetime columns to datetime2 type. Generate the sql scripts using the query below.

select distinct concat('alter table ', table_name, ' alter column ',  column_name, ' datetime2 ', 
case when(is_nullable = 'NO') then ' NOT ' else '' end, ' NULL;') 
from information_schema.columns where data_type = 'datetime';

SQL Server 2008 Datetime field overflow

4 Answers4