SqlDateTime overflow when using .ExecuteReader

Question

I am getting this error when executing a reader which is used on an asp page:

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

A date is selected from a calendar, and is set to a DateTime variable, which is then used as a parameter in an SQL command. It seems to randomly work, I select a date once and it works, I reload the page and select the same day and it might not work.

My relative code is below, I'm getting the error at the .ExecuteReader() line. I've checked the confirmedDate variable right before the reader is executed, and it does have a valid DateTime.

    DateTime confirmedDate = DateTime.Parse(dateSelected);
    SqlCommand command4 = new SqlCommand();
    command4.Connection = gconn;
    String sql4 = "SELECT MAX([Day]) as TheDay FROM Days WHERE User_ID = @User_ID AND [Day] < @dateSelected AND NOT EXISTS (SELECT 1 FROM Days WHERE User_ID = @User_ID AND [DAY] >= @dateSelected)";
    command4.CommandText = sql4;
    command4.Parameters.Add(new SqlParameter("@User_ID", ""));
    command4.Parameters.Add(new SqlParameter("@dateSelected", confirmedDate));


for (int i = 0; i < firstName.Count; i++ )
{
    command4.Parameters["@User_ID"].Value = userID[i];

    using (SqlDataReader reader = command4.ExecuteReader()) //error here
    {
        while (reader.Read())
        {
            if (reader.HasRows)
            {
                if (reader["TheDay"].ToString() == "") 
                {
                    dates.Add("NULL"); 
                }
                else
                {
                    dates.Add(reader["TheDay"].ToString());
                }
            }
        }
    }
}   

I've looked up other questions regarding this but can't find a solution that works. A valid DateTime value is always being passed in.

Answer 1

The possible range of a .net DateTime object is different from a Sql Server DateTime object. You should do some parameter validation before plugging it into your sql query.

Here is an example.

DateTime confirmedDate = DateTime.Parse(dateSelected);
if(confirmedDate > DateTime.Now) throw new ArgumentOutOfRangeException("Date time cannot be in the future");
if(confirmedDate.Year < 1990)  throw new ArgumentOutOfRangeException("Sorry, we only have data starting from the year 1990");

Also specify your data types in your parameters.

command4.Parameters.Add(new SqlParameter("@dateSelected", SqlDbType.DateTime){Value = confirmedDate}));

---

The range of a .Net DateTime

• Min DateTime - 00:00:00.0000000 UTC, January 1, 0001
• Max DateTime - 23:59:59.9999999 UTC, December 31, 9999

The possible range of a Sql DateTime

• Minimum value - 1/1/1753 12:00:00 AM
• Maximum value - 12/31/9999 11:59:59 PM

Answer 2

Replace the second @dateSelected with another name @dateselected2, and add it as third parameter.