question

kudlatiger avatar image
0 Votes"
kudlatiger asked OlafHelper-2800 answered

Fails only in production - SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59

I am having below code with proper NULL Check, still i get the error


   using (conn = new SqlConnection(interconnecting))
             {
                 using (var query = new SqlCommand(customerInsert))
                 {
                     query.Connection = conn;

                     query.Parameters.Add("@Id", SqlDbType.NVarChar, 150);
                     query.Parameters.Add("@lastSignInDateTime", SqlDbType.DateTime);

                     conn.Open();
                       
                           
                         query.Parameters["@Id"].Value = user.NimbusUserCrmId.ToString();                            
                         if (user.LastSignInDateTime != null)
                         {
                             query.Parameters["@lastSignInDateTime"].Value = user.LastSignInDateTime;
                         }
                         else
                         {                                
                             query.Parameters["@lastSignInDateTime"].Value = DBNull.Value;
                         }
                         query.ExecuteNonQuery();                          
                        
                     conn.Close();
                 }
             }


Which throws the below error

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59


sql-server-generaldotnet-csharp
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Please run your code through the Visual Studio debugger and check the value of user.LastSignInDateTime. It could be 1/1/0001 12:00:00 AM.

1 Vote 1 ·

Yes, but why can't i set it to null or empty? I do not want to store 1/1/0001 12:00:00 AM in DB

0 Votes 0 ·

1 Answer

OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered

I am having below code with proper NULL Check

The error has nothing to do with a NULL value, but with the datetime value.
SQL DateTime value must be 1/1/1753 and higher, as the error message clearly says.
If you want to store dates before you have to use the data type datetime2.
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver16
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.