Error in selection Datetimepicker date

Praveen Maurya

I want to filter my database using date time picker but when I am selecting date between 01-05-2018 and 01-06-2018 it shows only 01-05-2018 value please check is there any error

My SQL query is

SELECT DATE, ITEM_NAME, QTY, UNIT_SALE_PRICE, UNIT_PURCHASE_PRICE, TOTAL_SALE_PRICE, TOTAL_PURCHASE_PRICE, DIFFERENCE FROM PROFIT_LOSS WHERE (DATE BETWEEN '01-05-2018' AND '01-06-2018')

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P8y ago
Accepted answer
hi
try as below
SqlDataAdapter sda2 = new SqlDataAdapter("SELECT * FROM PROFIT_LOSS WHERE Date between ('"+d11.Value.ToString("yyyy-MM-dd")+"' and '"+d21.Value.ToString("yyyy-MM-dd")+"') )", con);
or
SqlDataAdapter sda2 = new SqlDataAdapter("SELECT * FROM PROFIT_LOSS WHERE (DATEDIFF(dy, '"+d11.Value.ToString("yyyy-MM-dd")+"' '"+d21.Value.ToString("yyyy-MM-dd")+"') > 0)", con);
Praveen Maurya8y ago
Thanks Rafans this is what i want
Hemant Srivastava8y ago
You missed comma :)

SqlDataAdapter sda2 = new SqlDataAdapter("SELECT * FROM PROFIT_LOSS WHERE (DATEDIFF(dy, '"+d11.Text+"', '"+d21.Text+"') > 0)", con);

and make sure d11.Text and d21.Text are in Date String format properly
Praveen Maurya8y ago
YOUR CODE WORKS FINE IN SQL QUERY BUT WHEN I AM USING THIS IN VS C#.NET IT SHOWS ERROR "Conversion failed when converting date and/or time from character string."
MY CODE WAS
SqlDataAdapter sda2 = new SqlDataAdapter("SELECT * FROM PROFIT_LOSS WHERE (DATEDIFF(dy, '"+d11.Text+"' '"+d21.Text+"') > 0)", con);
Hemant Srivastava8y ago
You need to use DATEDIFF() function

SELECT DATE, ITEM_NAME, QTY, UNIT_SALE_PRICE, UNIT_PURCHASE_PRICE, TOTAL_SALE_PRICE, TOTAL_PURCHASE_PRICE, DIFFERENCE
FROM PROFIT_LOSS
WHERE
DATEDIFF(dy, '01-05-2018' , '01-06-2018') > 0