Error - SqlDateTime overflow Must be between 1 1 1753 12 00 00 AM and 12 31 9999 11 59 59 PM

Question

I ve been using this piece of code I ve written and it s working in this most unclear manner  I wish to insert a row into the database which includes two columns of DateTime   myrow ApprovalDate   DateTime Now myrow ProposedDate   DateTime Now   And yet  when I update the database I receive this error      SqlDateTime overflow  Must be between 1 1 1753 12 00 00 AM and 12 31 9999 11 59 59 PM     I ve even tried copying an inserted value from the database and hard code it into the object being updated      I copied this value from the DB myrow ApprovalDate    Convert ToDateTime  2008-12-24 00 00 00 000      Still same error  the strange part is that the above trick worked for the first insert to the DB but failed from there on  Any ideas what s going on

User · Answer

In my case this error was raised because table date column is not null-able

As below:

Create Table #TempTable(
 ...
 ApprovalDate datatime not null.
 ...)

To avoid this error just make it null-able

 Create Table #TempTable(
 ...
 ApprovalDate datatime null.
 ...)

User · Answer

I find using the following works quite well for SQL min max dates after many DB related errors   DateTime rngMin    DateTime System Data SqlTypes SqlDateTime MinValue   DateTime rngMax    DateTime System Data SqlTypes SqlDateTime MaxValue

User · Answer

This error occurs if you are trying to set variable of type DateTime to null  Declare the variable as nullable  i e  DateTime    This will solve the problem

User · Answer

I am seeing the same thing   The error does not happen on insert of a row but on an update   the table I am referencing has two DateTime columns neither of which are nullable    I have gotten the scenario down to getting the row and immediately saving it  no data changes    The get works fine but the update fails   We are using NHibernate 3 3 1 4000

User · Answer

A DateTime in C  is a value type  not a reference type  and therefore cannot be null  It can however be the constant DateTime MinValue which is outside the range of Sql Servers DATETIME data type    Value types are guaranteed to always have a  default  value  of zero  without always needing to be explicitly set  in this case DateTime MinValue    Conclusion is you probably have an unset DateTime value that you are trying to pass to the database   DateTime MinValue   1 1 0001 12 00 00 AM DateTime MaxValue   23 59 59 9999999  December 31  9999                       exactly one 100-nanosecond tick                      before 00 00 00  January 1  10000   MSDN  DateTime MinValue      Regarding Sql Server     datetime   Date and time data from January 1  1753 through December 31  9999  to an accuracy of one three-hundredth of a second  equivalent to 3 33 milliseconds or 0 00333 seconds   Values are rounded to increments of  000   003  or  007 seconds      smalldatetime   Date and time data from January 1  1900  through June 6  2079  with accuracy to the minute  smalldatetime values with 29 998 seconds or lower are rounded down to the nearest minute  values with 29 999 seconds or higher are rounded up to the nearest minute    MSDN  Sql Server DateTime and SmallDateTime    Lastly  if you find yourself passing a C  DateTime as a string to sql  you need to format it as follows to retain maximum precision and to prevent sql server from throwing a similar error   string sqlTimeAsString   myDateTime ToString  yyyy-MM-ddTHH mm ss fff        Update  8 years later   Consider using the sql DateTime2 datatype which aligns better with the  net DateTime with date range 0001-01-01 through 9999-12-31 and time range 00 00 00 through 23 59 59 9999999   string dateTime2String   myDateTime ToString  yyyy-MM-ddTHH mm ss fffffff      MSDN datetime2   Transact-SQL

User · Answer

Usually this kind of error comes when you do DateTime conversion or parsing  Check the calendar setting in the server where the application is hosted  mainly the time zone and short date format  and ensure it s set to the right time zone for the location  Hope this would resolve the issue

User · Answer

If you are using NHibernate  check that appropriate DateTime properties that are nullable are set to nullable in the mappings

User · Answer

Usually this kind of error comes when you do DateTime conversion or parsing  Check the calendar setting in the server where the application is hosted  mainly the time zone and short date format  and ensure it s set to the right time zone for the location  Hope this would resolve the issue

User · Answer

That usually means a null is being posted to the query instead of your desired value  you might try to run the SQL Profiler to see exactly what is getting passed to SQL Server from linq

User · Answer

DateTime MinValue and DateTime MaxValue   DateTime MinValue   1 1 0001 12 00 00 AM  DateTime MaxValue   23 59 59 9999999  December 31  9999                    exactly one 100-nanosecond tick                   before 00 00 00  January 1  10000

User · Answer

Sometimes in order to write less code it is used to have SQL server set fields like date  time and ID on insert by setting the default value for fields to GETDATE   or NEWID     In such cases Auto Generated Value property of those fields in entity classes should be set to true   This way you do not need to set values in code  preventing energy consumption     and never see that exception

User · Answer

The code you have for the two columns looks ok   Look for any other datetime columns on that mapping class   Also  enable logging on the datacontext to see the query and parameters   dc Log   Console Out    DateTime is initialized to c  s 0 - which is 0001-01-01   This is transmitted by linqtosql to the database via sql string literal    0001-01-01    Sql cannot parse a T-Sql datetime from this date   There s a couple ways to deal with this    Make sure you initialize all date times with a value that SQL can handle  such as Sql s 0   1900-01-01   Make sure any date times that may occasionally be omitted are nullable datetimes

User · Answer

Beware when comparing a  Net DateTime to SqlDateTime MinValue or MaxValue  For example  the following will throw an exception   DateTime dte   new DateTime 1000  1  1   if  dte  gt   SqlDateTime MinValue        do something   The reason is that MinValue returns a SqlDateTime  not a DateTime  So  Net tries to convert dte to a SqlDateTime for comparison and because it s outside the acceptable SqlDateTime range it throws the exception   One solution to this is to compare your DateTime to SqlDateTime MinValue Value

User · Answer

The code you have for the two columns looks ok   Look for any other datetime columns on that mapping class   Also  enable logging on the datacontext to see the query and parameters   dc Log   Console Out    DateTime is initialized to c  s 0 - which is 0001-01-01   This is transmitted by linqtosql to the database via sql string literal    0001-01-01    Sql cannot parse a T-Sql datetime from this date   There s a couple ways to deal with this    Make sure you initialize all date times with a value that SQL can handle  such as Sql s 0   1900-01-01   Make sure any date times that may occasionally be omitted are nullable datetimes

User · Answer

Sometimes in order to write less code it is used to have SQL server set fields like date  time and ID on insert by setting the default value for fields to GETDATE   or NEWID     In such cases Auto Generated Value property of those fields in entity classes should be set to true   This way you do not need to set values in code  preventing energy consumption     and never see that exception

User · Answer

Beware when comparing a  Net DateTime to SqlDateTime MinValue or MaxValue  For example  the following will throw an exception   DateTime dte   new DateTime 1000  1  1   if  dte  gt   SqlDateTime MinValue        do something   The reason is that MinValue returns a SqlDateTime  not a DateTime  So  Net tries to convert dte to a SqlDateTime for comparison and because it s outside the acceptable SqlDateTime range it throws the exception   One solution to this is to compare your DateTime to SqlDateTime MinValue Value

User · Answer

A DateTime in C  is a value type  not a reference type  and therefore cannot be null  It can however be the constant DateTime MinValue which is outside the range of Sql Servers DATETIME data type    Value types are guaranteed to always have a  default  value  of zero  without always needing to be explicitly set  in this case DateTime MinValue    Conclusion is you probably have an unset DateTime value that you are trying to pass to the database   DateTime MinValue   1 1 0001 12 00 00 AM DateTime MaxValue   23 59 59 9999999  December 31  9999                       exactly one 100-nanosecond tick                      before 00 00 00  January 1  10000   MSDN  DateTime MinValue      Regarding Sql Server     datetime   Date and time data from January 1  1753 through December 31  9999  to an accuracy of one three-hundredth of a second  equivalent to 3 33 milliseconds or 0 00333 seconds   Values are rounded to increments of  000   003  or  007 seconds      smalldatetime   Date and time data from January 1  1900  through June 6  2079  with accuracy to the minute  smalldatetime values with 29 998 seconds or lower are rounded down to the nearest minute  values with 29 999 seconds or higher are rounded up to the nearest minute    MSDN  Sql Server DateTime and SmallDateTime    Lastly  if you find yourself passing a C  DateTime as a string to sql  you need to format it as follows to retain maximum precision and to prevent sql server from throwing a similar error   string sqlTimeAsString   myDateTime ToString  yyyy-MM-ddTHH mm ss fff        Update  8 years later   Consider using the sql DateTime2 datatype which aligns better with the  net DateTime with date range 0001-01-01 through 9999-12-31 and time range 00 00 00 through 23 59 59 9999999   string dateTime2String   myDateTime ToString  yyyy-MM-ddTHH mm ss fffffff      MSDN datetime2   Transact-SQL

User · Answer

In my case this error was raised because table date column is not null-able

As below:

Create Table #TempTable(
 ...
 ApprovalDate datatime not null.
 ...)

To avoid this error just make it null-able

 Create Table #TempTable(
 ...
 ApprovalDate datatime null.
 ...)

User · Answer

Use extension method    public static object ToSafeDbDateDBnull this object objectstring                try                       if   DateTime objectstring  gt   SqlDateTime MinValue                                return objectstring                            else                               return DBNull Value                                  catch  Exception                         return DBNull Value                    DateTime objdte   new DateTime 1000  1  1   dte ToSafeDbDateDBnull

User · Answer

DateTime MinValue and DateTime MaxValue   DateTime MinValue   1 1 0001 12 00 00 AM  DateTime MaxValue   23 59 59 9999999  December 31  9999                    exactly one 100-nanosecond tick                   before 00 00 00  January 1  10000

User · Answer

A DateTime in C  is a value type  not a reference type  and therefore cannot be null  It can however be the constant DateTime MinValue which is outside the range of Sql Servers DATETIME data type    Value types are guaranteed to always have a  default  value  of zero  without always needing to be explicitly set  in this case DateTime MinValue    Conclusion is you probably have an unset DateTime value that you are trying to pass to the database   DateTime MinValue   1 1 0001 12 00 00 AM DateTime MaxValue   23 59 59 9999999  December 31  9999                       exactly one 100-nanosecond tick                      before 00 00 00  January 1  10000   MSDN  DateTime MinValue      Regarding Sql Server     datetime   Date and time data from January 1  1753 through December 31  9999  to an accuracy of one three-hundredth of a second  equivalent to 3 33 milliseconds or 0 00333 seconds   Values are rounded to increments of  000   003  or  007 seconds      smalldatetime   Date and time data from January 1  1900  through June 6  2079  with accuracy to the minute  smalldatetime values with 29 998 seconds or lower are rounded down to the nearest minute  values with 29 999 seconds or higher are rounded up to the nearest minute    MSDN  Sql Server DateTime and SmallDateTime    Lastly  if you find yourself passing a C  DateTime as a string to sql  you need to format it as follows to retain maximum precision and to prevent sql server from throwing a similar error   string sqlTimeAsString   myDateTime ToString  yyyy-MM-ddTHH mm ss fff        Update  8 years later   Consider using the sql DateTime2 datatype which aligns better with the  net DateTime with date range 0001-01-01 through 9999-12-31 and time range 00 00 00 through 23 59 59 9999999   string dateTime2String   myDateTime ToString  yyyy-MM-ddTHH mm ss fffffff      MSDN datetime2   Transact-SQL

User · Answer

I am seeing the same thing   The error does not happen on insert of a row but on an update   the table I am referencing has two DateTime columns neither of which are nullable    I have gotten the scenario down to getting the row and immediately saving it  no data changes    The get works fine but the update fails   We are using NHibernate 3 3 1 4000

User · Answer

If you put Datetime nullable like DateTime  in your model it doesn t throw exception  I solved the problem like this in my case

User · Answer

That usually means a null is being posted to the query instead of your desired value  you might try to run the SQL Profiler to see exactly what is getting passed to SQL Server from linq

User · Answer

Use extension method    public static object ToSafeDbDateDBnull this object objectstring                try                       if   DateTime objectstring  gt   SqlDateTime MinValue                                return objectstring                            else                               return DBNull Value                                  catch  Exception                         return DBNull Value                    DateTime objdte   new DateTime 1000  1  1   dte ToSafeDbDateDBnull

User · Answer

This error occurs if you are trying to set variable of type DateTime to null  Declare the variable as nullable  i e  DateTime    This will solve the problem

User · Answer

If you put Datetime nullable like DateTime  in your model it doesn t throw exception  I solved the problem like this in my case

User · Answer

The code you have for the two columns looks ok   Look for any other datetime columns on that mapping class   Also  enable logging on the datacontext to see the query and parameters   dc Log   Console Out    DateTime is initialized to c  s 0 - which is 0001-01-01   This is transmitted by linqtosql to the database via sql string literal    0001-01-01    Sql cannot parse a T-Sql datetime from this date   There s a couple ways to deal with this    Make sure you initialize all date times with a value that SQL can handle  such as Sql s 0   1900-01-01   Make sure any date times that may occasionally be omitted are nullable datetimes

User · Answer

That usually means a null is being posted to the query instead of your desired value  you might try to run the SQL Profiler to see exactly what is getting passed to SQL Server from linq

User · Answer

The code you have for the two columns looks ok   Look for any other datetime columns on that mapping class   Also  enable logging on the datacontext to see the query and parameters   dc Log   Console Out    DateTime is initialized to c  s 0 - which is 0001-01-01   This is transmitted by linqtosql to the database via sql string literal    0001-01-01    Sql cannot parse a T-Sql datetime from this date   There s a couple ways to deal with this    Make sure you initialize all date times with a value that SQL can handle  such as Sql s 0   1900-01-01   Make sure any date times that may occasionally be omitted are nullable datetimes

User · Answer

That usually means a null is being posted to the query instead of your desired value  you might try to run the SQL Profiler to see exactly what is getting passed to SQL Server from linq

User · Answer

I find using the following works quite well for SQL min max dates after many DB related errors   DateTime rngMin    DateTime System Data SqlTypes SqlDateTime MinValue   DateTime rngMax    DateTime System Data SqlTypes SqlDateTime MaxValue

User · Answer

A DateTime in C  is a value type  not a reference type  and therefore cannot be null  It can however be the constant DateTime MinValue which is outside the range of Sql Servers DATETIME data type    Value types are guaranteed to always have a  default  value  of zero  without always needing to be explicitly set  in this case DateTime MinValue    Conclusion is you probably have an unset DateTime value that you are trying to pass to the database   DateTime MinValue   1 1 0001 12 00 00 AM DateTime MaxValue   23 59 59 9999999  December 31  9999                       exactly one 100-nanosecond tick                      before 00 00 00  January 1  10000   MSDN  DateTime MinValue      Regarding Sql Server     datetime   Date and time data from January 1  1753 through December 31  9999  to an accuracy of one three-hundredth of a second  equivalent to 3 33 milliseconds or 0 00333 seconds   Values are rounded to increments of  000   003  or  007 seconds      smalldatetime   Date and time data from January 1  1900  through June 6  2079  with accuracy to the minute  smalldatetime values with 29 998 seconds or lower are rounded down to the nearest minute  values with 29 999 seconds or higher are rounded up to the nearest minute    MSDN  Sql Server DateTime and SmallDateTime    Lastly  if you find yourself passing a C  DateTime as a string to sql  you need to format it as follows to retain maximum precision and to prevent sql server from throwing a similar error   string sqlTimeAsString   myDateTime ToString  yyyy-MM-ddTHH mm ss fff        Update  8 years later   Consider using the sql DateTime2 datatype which aligns better with the  net DateTime with date range 0001-01-01 through 9999-12-31 and time range 00 00 00 through 23 59 59 9999999   string dateTime2String   myDateTime ToString  yyyy-MM-ddTHH mm ss fffffff      MSDN datetime2   Transact-SQL

User · Answer

If you are using NHibernate  check that appropriate DateTime properties that are nullable are set to nullable in the mappings

[asp.net] Error - SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

Examples related to asp.net

Examples related to linq-to-sql

Examples related to sql-server-2008