CODEWITHSUNDEEP
c#asp.netsql-serverdate
Michael
Michael

Reputation: 85

Getting Error When use Date to Search

        cmdDateReceivingStock = new SqlCommand("Select Distinct Date FROM 
        OrderDetails WHERE item_Received ='No'", conStock);

        SqlDataAdapter da = new SqlDataAdapter(cmdDateReceivingStock);
        DataSet ds = new DataSet();
        da.Fill(ds);

        ddlOrderDate.DataTextFormatString = "{0:dd-MM-yyyy}";
        ddlOrderDate.DataSource = ds;
        ddlOrderDate.DataTextField = "Date";
        ddlOrderDate.DataBind();

After I success convert the date into dropdownlist,but I getting this error"Conversion failed when converting date and/or time from character string." when I use the date to retrieve data from database.

Upvotes: 2

Views: 98

Answers (4)

Ramesh Rajendran
Ramesh Rajendran

Reputation: 38683

Try this way in sql query 

SELECT CONVERT(char(10), GetDate(),126)

Change your query with using DATE_FORMAT() 

cmdDateReceivingStock = new SqlCommand("Select Distinct DATE_FORMAT(Date , '%d/%m/%Y') FROM 
        OrderDetails WHERE item_Received ='No'", conStock);

for more date formating

Upvotes: 1

user3035897
user3035897

Reputation: 31

Try this in your sql query.

 cmdDateReceivingStock = new SqlCommand("Select Distinct CONVERT(VARCHAR,Date,103) FROM 
        OrderDetails WHERE item_Received ='No'", conStock);

Additional reading: To get different date and datetime formats refer this link

Upvotes: 2

mojoblanco
mojoblanco

Reputation: 723

You can try 

ddlOrderDate.DataTextFormatString = "{yyyy-MM-dd}";

This will format the date for you and remove the zeros

Upvotes: 3

user3104183
user3104183

Reputation: 408

Your SELECT statement is fine, you have to modify the date format in your ddl: 

ddlOrderDate.DataTextFormatString = "{0:dd-MM-yyyy}";

Upvotes: 2

Related Questions