SqlTypeException: SqlDateTime overflow

by

Problem

I am executing a SQL query and trying to set a datetime column to DateTime.MinValue. I’m getting the following exception:

System.Data.SqlTypes.SqlTypeException: ‘SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.’

The problem is that DateTime.MinValue is 1/1/0001 12:00:00 AM and the SQL Server DateTime minimum value is 1/1/1753 12:00 AM.

Solution

Use System.Data.SqlTypes.SqlDateTime.MinValue instead of DateTime.MinValue when you’re specifying the query parameters:

using(SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString))
{
	using(SqlCommand cmd = new SqlCommand(@"INSERT INTO Articles2 (Title, CreatedDateTime, CreatedBy, ModifiedDateTime) VALUES (@Title, @CreatedDateTime, @CreatedBy, @ModifiedDateTime)", con))
	{
		cmd.Parameters.AddWithValue("@Title", "SqlTypeException: SqlDateTime overflow");
		cmd.Parameters.AddWithValue("@CreatedDateTime", DateTime.Now);
		cmd.Parameters.AddWithValue("@CreatedBy", "Mak");
		cmd.Parameters.AddWithValue("@ModifiedDateTime", System.Data.SqlTypes.SqlDateTime.MinValue);

		con.Open();

		cmd.ExecuteNonQuery();
	}
}
Code language: C# (cs)

Related Articles