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
– Ian, Commented Jan 15, 2016 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
– pfinferno, Commented Jan 15, 2016 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
– pfinferno, Commented Jan 15, 2016 at 15:44

Soner Gönül · Accepted Answer · 2016-01-15 15:50:39Z

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, 2016 at 15:50

answered Jan 15, 2016 at 15:45

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Joel Coehoorn Over a year ago

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.

pfinferno Over a year ago

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?

Soner Gönül Over a year ago

@pfinferno What is an “index out of range” exception, and how do I fix it?

Joel Coehoorn Over a year ago

@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#.

Jaydip Jadhav Over a year ago

@pfinferno your column name in Select statement is TheDay

|

Collectives™ on Stack Overflow

DateTime Conversion error when executing SqlDataReader

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related