none
Conversion failed when converting datetime from character string. RRS feed

  • Question

  • Hi everybody

    i have a problem with my C# application. I get an error when i get string data from a text field and then pass this to an SQL Query.

    The strange thing is that if i hard code the string data into a variable, which in my example is a date,
    eg "2007-11-01 00:00:00" the query works fine, but if i pass the exact string  "2007-11-01 00:00:00" from a text field, the application fails and gives the follwing error message....

    "SqlException was unhandled by user code" - Conversion failed when converting datetime from character string.

    i find this rather odd, as both my hardcoded date values and the date from the text box are both strings anyway !

    i have a method that i pass in the string values of the text boxes, which are both dates, and then i include them to my SQL statement, please see my code below...

    Code Block

    // my method with dates that are strings as parameters

    public availableHotel[] Hotels_Date_Availability_Checker(string fromDate, string toDate)            

      string from_Date = "";

                string to_Date = "";

                if (Date_From.Text == "" && Date_To.Text == "")
                {

    // hard coding for testing

                   from_Date = "2007-11-01 00:00:00";
                   to_Date = "2007-12-30 00:00:00";
                }
                else
                {

                    // assigning the values from the text boxes - what i want !!

                    from_Date = fromDate;
                    to_Date = toDate;
                }


    and here is my SQL query...

    Code Block

    checkDatesQuery = "SELECT r.Room_Number, r.Room_Capacity, r.Available, h.Hotel_name, h.Location, h.Price_Per_Room FROM Room AS r INNER JOIN hotel AS h ON r.Hotel_ID = h.Hotel_ID WHERE (r.Move_In_Date > CONVERT(DATETIME, '" + from_Date + "', 102)) AND (r.Move_Out_Date < CONVERT(DATETIME, '" + to_Date + "', 102)) AND h.Hotel_name = '" + Hotel_name + "'";


    As you can see im parsing from strings anyway so i am really scratching my head with this one.

    any help is most appreciated !!

    Truegilly

    Sunday, November 25, 2007 9:51 PM

Answers

  • First you are not using parameters....

    Secondly you should always work with DateTime becuase things just format correctly!

    Third you are passing the variable names not the variable values to your SQL string.

     

     

    Try this instead...

     

    Code Block

    DateTime from_Date = DateTime.Now().ToString();

    DateTime to_Date = DateTime.Parse("12/31/2007");

    try

    {

    DateTime from_Date = DateTime.Parse(fromDate);
    DateTime to_Date = DateTime.Parse(fromDate);

    }

    catch

    {
    // We already set the default values

    }

     

     

    checkDatesQuery =

     

    "SELECT r.Room_Number, r.Room_Capacity, r.Available, h.Hotel_name, h.Location, h.Price_Per_Room FROM Room AS r INNER JOIN hotel AS h ON r.Hotel_ID = h.Hotel_ID WHERE (r.Move_In_Date > " +  from_Date.ToShortDateString() + ") AND (r.Move_Out_Date <= " + to_Date.ToShortDateString() + ")";

     

     

     

     

    To do this same thing using parameters you would do this

    checkDatesQuery =

     

    Code Block

    "SELECT r.Room_Number, r.Room_Capacity, r.Available, h.Hotel_name, h.Location, h.Price_Per_Room FROM Room AS r INNER JOIN hotel AS h ON r.Hotel_ID = h.Hotel_ID WHERE (r.Move_In_Date > @from_Date) AND (r.Move_Out_Date <= @to_Date)";

     

    cmd.Parameters.Add("from_Date", SqlDbType.DateTime);

    cmd.Parameters["from_Date"].Value = from_Date.ToShortString();

    cmd.Parameters.Add("to_Date", SqlDbType.DateTime);

    cmd.Parameters["to_Date"].Value = to_Date.ToShortString();

     

     

     

     

     

     

     

    Monday, November 26, 2007 3:48 AM

All replies

  • Create a parameterized query and save it in a stored procedure, then create a strong typed dataset, add a datatable and specify the stored procedure as the data source. This will generate a wrapper class of the stored procedure that let you call it with DateTime parameters.

    Monday, November 26, 2007 3:23 AM
  • First you are not using parameters....

    Secondly you should always work with DateTime becuase things just format correctly!

    Third you are passing the variable names not the variable values to your SQL string.

     

     

    Try this instead...

     

    Code Block

    DateTime from_Date = DateTime.Now().ToString();

    DateTime to_Date = DateTime.Parse("12/31/2007");

    try

    {

    DateTime from_Date = DateTime.Parse(fromDate);
    DateTime to_Date = DateTime.Parse(fromDate);

    }

    catch

    {
    // We already set the default values

    }

     

     

    checkDatesQuery =

     

    "SELECT r.Room_Number, r.Room_Capacity, r.Available, h.Hotel_name, h.Location, h.Price_Per_Room FROM Room AS r INNER JOIN hotel AS h ON r.Hotel_ID = h.Hotel_ID WHERE (r.Move_In_Date > " +  from_Date.ToShortDateString() + ") AND (r.Move_Out_Date <= " + to_Date.ToShortDateString() + ")";

     

     

     

     

    To do this same thing using parameters you would do this

    checkDatesQuery =

     

    Code Block

    "SELECT r.Room_Number, r.Room_Capacity, r.Available, h.Hotel_name, h.Location, h.Price_Per_Room FROM Room AS r INNER JOIN hotel AS h ON r.Hotel_ID = h.Hotel_ID WHERE (r.Move_In_Date > @from_Date) AND (r.Move_Out_Date <= @to_Date)";

     

    cmd.Parameters.Add("from_Date", SqlDbType.DateTime);

    cmd.Parameters["from_Date"].Value = from_Date.ToShortString();

    cmd.Parameters.Add("to_Date", SqlDbType.DateTime);

    cmd.Parameters["to_Date"].Value = to_Date.ToShortString();

     

     

     

     

     

     

     

    Monday, November 26, 2007 3:48 AM