locked
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value error RRS feed

  • Question

  • User-1506965535 posted

    I am adding a date as "09/10/2014" into the textbox and clicking the submit button but getting error as

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value error

    Below is my query generated while debugging

    select * from WMS_BIN_STATUS_TRACK where 1!=1 or Current_Item_Exp_Dt = convert(datetime, '09/10/2014', 103)

    and below is the full code:-

    protected void btnTrack_OnClick(Object sender, EventArgs e)
        {
            string whereClause = "1!=1";
    
            if (ddlBin.SelectedValue != "0")
            {
                whereClause = whereClause + "or location_name='" + ddlBin.SelectedValue + "'";
            }
            if (ddlItem.SelectedValue != "0")
            {
                whereClause = whereClause + "or Current_Item_code='" + ddlItem.SelectedValue + "'";
            }
            if (txtBatch.Text != "")
            {
                whereClause = whereClause
                    + " or Current_Item_Batch " + (ddlmathsign.SelectedValue == "Equal" ? (" = '" + txtBatch.Text + "'") : (" like '%" + txtBatch.Text + "%'"));
            }
            if (txtExpCal.Value != "")
            {
                whereClause = whereClause + "or Current_Item_Exp_Dt " + (ddlAssignvalue.SelectedValue == "Greater than" ? ">" : (ddlAssignvalue.SelectedValue == "Less than" ? "<" :
                          (ddlAssignvalue.SelectedValue == "Equal to" ? "=" : (ddlAssignvalue.SelectedValue == "Greater than equal to" ? ">=" : "<=")))) + "convert(datetime, '" + txtExpCal.Value + "', 103)";
            }
    
            if (ddlBin.SelectedValue == "0" && ddlItem.SelectedValue == "0" && txtBatch.Text == "" && txtExpCal.Value == "")
            {
                BindGrid();
            }
    
            else
            {
                string query = "select * from WMS_BIN_STATUS_TRACK where " + whereClause;
               
                SqlDataAdapter da = new SqlDataAdapter(query, strConnString);
                DataTable dt = new DataTable();
                da.Fill(dt);
                GrdBinStockTracker.DataSource = dt;
                GrdBinStockTracker.DataBind();
            }
        }

    Tuesday, December 8, 2015 1:17 PM

Answers

  • User-1506965535 posted

    Are you 100% it fails with 09/10/2014? Or could it be that the Current_Item_Exp_Dt column is a char/varchar rather than a datetime column?

    Yes it fails for the date which is mentioned. Also Current_Item_Exp_Dt is a datetime column.

    Any other thing which might cause this issue ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 8, 2015 4:40 PM
  • User753101303 posted

    Ok so the error message is caused by the conversion of the Current_Item_Exp_Dt to a datetime. A quick fix could be (depending on date strings found inside this column) :

    convert(datetime,Current_Item_Exp_Dt,103) = convert(datetime, '09/10/2014', 103)

    Of course, the real fix would be to change the design and to use a real datetime column. Always use the proper type to store your data. Sometimes beginners want to store dates using a "particular format" but this is not how it works. Instead you should work as much as you can with the native date value and formatting should be done just before showing the value to users (as it should be converted back when  reading field input). This way you can work with date(time) without any concern and are able to support whatever country convention you want and more efficiently than when storing date and times as strings.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 9, 2015 10:44 AM

All replies

  • User753101303 posted

    Hi,

    It should really work with this particular value. It would fail with the same message with convert(datetime,'09/13/2014',103) for example (as the month is not valid). Are you 100% it fails with 09/10/2014? Or could it be that the Current_Item_Exp_Dt column is a char/varchar rather than a datetime column?

    Generally speaking search for "parameterized queries" and it's likely best to do this string to date conversion before transmitting the value to SQL Server.

    Tuesday, December 8, 2015 1:38 PM
  • User-1506965535 posted

    Are you 100% it fails with 09/10/2014? Or could it be that the Current_Item_Exp_Dt column is a char/varchar rather than a datetime column?

    Yes it fails for the date which is mentioned. Also Current_Item_Exp_Dt is a datetime column.

    Any other thing which might cause this issue ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 8, 2015 4:40 PM
  • User-1506965535 posted

    Hello PatrieSc,

    I cross checked the column datatype for Current_Item_Exp_Dt and it is varchar datatype.

    Wednesday, December 9, 2015 5:16 AM
  • User753101303 posted

    Ok so the error message is caused by the conversion of the Current_Item_Exp_Dt to a datetime. A quick fix could be (depending on date strings found inside this column) :

    convert(datetime,Current_Item_Exp_Dt,103) = convert(datetime, '09/10/2014', 103)

    Of course, the real fix would be to change the design and to use a real datetime column. Always use the proper type to store your data. Sometimes beginners want to store dates using a "particular format" but this is not how it works. Instead you should work as much as you can with the native date value and formatting should be done just before showing the value to users (as it should be converted back when  reading field input). This way you can work with date(time) without any concern and are able to support whatever country convention you want and more efficiently than when storing date and times as strings.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 9, 2015 10:44 AM
  • User-1506965535 posted

    Yes, actually the datatype was the root cause for the error, changed it to the DateTime. Thus the error went.

    Thanks for letting me know where I was making the mistake.

    Wednesday, December 9, 2015 10:51 AM