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.

As a first step to solve the problem I would try to set the Parameters with an explict DataType and never use a method to decide for me which is the correct DataType for the parameter. — Steve, Feb 23 '16 at 17:06

Igor · Accepted Answer · 2016-02-23T17:16:39.820

3

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

edited Feb 23 '16 at 17:16

answered Feb 23 '16 at 17:10

Igor

60,821
10
100
175

1

Or change from `datetime` to `datetime2` which has a better range – Panagiotis Kanavos Feb 23 '16 at 17:18
Turns out the calendar the date was coming from was sometimes Null because of certain situations, which resulted in the date being 0001 of Jan. 1st. Fixed that and did parameter validation as you said since that's the proper way. – pfinferno Feb 23 '16 at 17:18
@PanagiotisKanavos - absolutely agree if either precision is of concern or *(as you mentioned)* you need to extend the possible range. – Igor Feb 23 '16 at 17:21

score 1 · Answer 2 · answered Feb 23 '16 at 17:11

1

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

answered Feb 23 '16 at 17:11

Alex Suleap

559
4
12

SqlDateTime overflow when using .ExecuteReader

2 Answers2