CODEWITHSUNDEEP
c#sqlasp.netdatetimesql-server-2005
HEEN
HEEN

Reputation: 4721

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

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();
    }
}

NOTE As I am working on local condition only to test, SQL Injection is not a matter of concern here.

Also, Is this a issue related to different datetime. ?

Upvotes: 2

Views: 1273

Answers (2)

Sachu
Sachu

Reputation: 7766

since your column is varchar and not able to change to datetime you need to convert that column also to datetime for the condition.

Please check the below query

    whereClause = whereClause + "or convert(datetime,Current_Item_Exp_Dt,103) " 
    + (ddlAssignvalue.SelectedValue == "Greater than" ? ">" : 
     (ddlAssignvalue.SelectedValue == "Less than" ? "<" : 
    (ddlAssignvalue.SelectedValue == "Equal to" ? "=" : 
    (ddlAssignvalue.SelectedValue == "Greater than equal to" ? ">=" : "<=")))) + 
"convert(datetime, '" + txtExpCal.Value + "', 103)";

If you are not sure Current_Item_Exp_Dt always contain a valid date you can use below query build as per DarkNight

   whereClause = whereClause + "or (CASE WHEN ISDATE(Current_Item_Exp_Dt)=1 
           THEN convert(datetime,Current_Item_Exp_Dt,103)  ELSE NULL END) " 
    + (ddlAssignvalue.SelectedValue == "Greater than" ? ">" : 
     (ddlAssignvalue.SelectedValue == "Less than" ? "<" : 
    (ddlAssignvalue.SelectedValue == "Equal to" ? "=" : 
    (ddlAssignvalue.SelectedValue == "Greater than equal to" ? ">=" : "<=")))) + 
"convert(datetime, '" + txtExpCal.Value + "', 103)";

Upvotes: 2

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

The issue is with the data in Current_Item_Exp_Dt column.

Here is a way to reproduce it.

create table #tt(dd char(10))
insert into #tt values('13/13/2014')

select 1 from #tt where dd = convert(datetime, '09/10/2014', 103)

You can use this query to identify records in incorrect date format.

SELECT Current_Item_Exp_Dt FROM WMS_BIN_STATUS_TRACK WHERE ISDATE(Current_Item_Exp_Dt)=0

This is in possible workaround. Change you where conidtion to..

WHERE (CASE WHEN ISDATE(Current_Item_Exp_Dt)=1 
               THEN Current_Item_Exp_Dt 
               ELSE NULL END) = CONVERT(DATETIME, '09/10/2014', 103)

Your final query should be

SELECT Current_Item_Exp_Dt 
FROM WMS_BIN_STATUS_TRACK 
WHERE 1!=1 or (CASE WHEN ISDATE(Current_Item_Exp_Dt)=1 
               THEN Current_Item_Exp_Dt 
               ELSE NULL END) = CONVERT(DATETIME, '09/10/2014', 103)

Upvotes: 2

Related Questions