DateTime Conversion error when executing SqlDataReader

Question

I'm getting an issue when executing a reader to retrieve some DateTimes from a table.

First, I have one page transferring over some variables to another page:

//calStart.SelectedDate is a DateTime value
Response.Redirect("SpecialReports_Results.aspx?userListValues=" + userListValues + "&groupListValues=" + groupListValues + "&calSelected=" + calStart.SelectedDate);

Then, on the new page:

    //To retrieve the values that were sent over
    string userListValues = Request.QueryString["userListValues"];
    string groupListValues = Request.QueryString["groupListValues"];
    string dateSelected = Request.QueryString["calSelected"];

    // SQL Server connection stuff + string argument
    SqlCommand command2 = new SqlCommand();
    command2.Connection = gconn;

    String sql2 = "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')";
    command2.CommandText = sql2;

    command2.Parameters.Add(new SqlParameter("@User_ID", ""));
    command2.Parameters.Add(new SqlParameter("@dateSelected", dateSelected));

    List<string> dates = new List<string>();

    //userID is a List<string>
    foreach (string str in userID)
    {
        command2.Parameters["@User_ID"].Value = str;

        using (SqlDataReader reader = command2.ExecuteReader())
        {
            while (reader.Read()) //Getting error here: Conversion failed when converting datetime from character string.
            {
                if (reader.HasRows)
                {
                    dates.Add(reader["Day"].ToString());
                }
            }
        }
    }

The table Days is set up like so:

User_ID | Day
----------------------------------
10      | 2010-11-09 00:00:00.000
20      | 2015-12-06 00:00:00.000
30      | 2012-01-12 00:00:00.000
40      | 2013-07-23 00:00:00.000

The Day column is of type DateTime.

I have tried converting the string dateSelected and the List<string> dates to DateTime by doing:

DateTime confirmedDate = DateTime.Parse(dateSelected);
List<DateTime> dates = new List<DateTime>()

But I get the same error.

Note: The SQL statement does work when executed in Microsoft's SQL Server Management Studio.

Did you check the value of the `dateSelected` before `DateTime.Parse`? what is that? and what is the data type? string? — Ian, Jan 15 '16 at 15:42
Yes, it is the same value but just as a string. Example: `"12/1/2015 12:00:00 AM"` is a string and after the parse it becomes `{12/1/2015 12:00:00 AM}` that is System.DateTime — pfinferno, Jan 15 '16 at 15:43
In the foreach loop, I put a comment next to the `while(reader.Read())` line which is where the error occurs. — pfinferno, Jan 15 '16 at 15:44

Soner Gönül · Accepted Answer · 2016-01-15T15:50:39.623

2

I think you need to delete single quotes on your '@dateSelected'.

With that, your code see it as a string literal, not a parameter.

String sql2 = "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)";

Since there is no implicit conversation from string to datetime, your reader try to convert this @dateSelected string literal to datetime and it fails.

edited Jan 15 '16 at 15:50

answered Jan 15 '16 at 15:45

Soner Gönül

97,193
102
206
364

2

This is correct. Additionally, it's good practice to also parse the `Request.QueryString["calSelected"]` value to a C# DateTime object before assigning it to the parameter. Otherwise, the SqlParameter constructor will create an `nvarchar` parameter, which can have painful consequences when it comes time to match the value with an index. – Joel Coehoorn Jan 15 '16 at 15:50
I removed the single quotes as you said. It gets past the first error, but now I get a `IndexOutOfRangeException was unhandled by user code` error on `dates.Add(reader["Day"].ToString());`. Some of the results should be Null which should not get added to the list. That's why I added `reader.HasRows` but maybe that isn't correct? – pfinferno Jan 15 '16 at 15:50
1

@pfinferno [What is an “index out of range” exception, and how do I fix it?](http://stackoverflow.com/q/20940979/447156) – Soner Gönül Jan 15 '16 at 15:51
2

@pfinferno No, the `HasRows` check is not what you want. A NULL value still produces a row. You can check for `DBNull.Value` instead, or (even better) you can add a condition to your sql `WHERE` clause so that those records are filtered out by the database before coming back to C#. – Joel Coehoorn Jan 15 '16 at 15:53
2

@pfinferno your column name in Select statement is `TheDay ` – Jaydip Jadhav Jan 15 '16 at 15:53
Yup that was it. Thank you. Also, going to make changes as @JoelCoehoorn said to filter out the null values beforehand. – pfinferno Jan 15 '16 at 15:57

DateTime Conversion error when executing SqlDataReader

1 Answers1