CODEWITHSUNDEEP
sqlsql-server-2008between
Pradnya Bolli
Pradnya Bolli

Reputation: 1943

Find record between two dates

When I write below query it gives record .

SELECT [srno],[order_no],[order_date],[supplier_name],[item_code],[item_name],[quntity]
FROM [first].[dbo].[Purchase_Order]
WHERE order_date BETWEEN '22/04/2015' AND '4/05/2015'

In this query if I don't add 0 in '4/05/2015' it returns record. But when I add 0 to the date i.e. '04/05/2015' it doesn't give any records.

SELECT [srno],[order_no],[order_date],[supplier_name],[item_code],[item_name],[quntity]
FROM [first].[dbo].[Purchase_Order]
WHERE order_date BETWEEN '22/04/2015' AND '04/05/2015'

Upvotes: 0

Views: 708

Answers (2)

ughai
ughai

Reputation: 9890

The reason it's not working because SQL is trying to do a string comparison because both your types are string types, But what you really want to do a date comparison.

You should do something like this. Since you only need date part you can strip off the time and use style 103 for your format dd/mm/yyyy.

WHERE CONVERT(DATETIME,LEFT(order_date,10),103)
BETWEEN CONVERT(DATETIME,'20150422') AND CONVERT(DATETIME,'20150504')

Alternately you can use this as well if your order_date has dates like this 5/4/2015 03:20:24PM

WHERE CONVERT(DATETIME,LEFT(order_Date,CHARINDEX(' ', order_Date) - 1),103) 
BETWEEN CONVERT(DATETIME,'20150422') AND CONVERT(DATETIME,'20150504')

A long term solution is to change your column order_date to DATE/DATETIME

Upvotes: 1

Dhaval
Dhaval

Reputation: 2379

It Better to Cast it to date rather than depend on IMPLICIT conversion

SELECT [srno],[order_no],[order_date],[supplier_name],[item_code],
 [item_name],[quntity] FROM [first].[dbo].[Purchase_Order] where 
convert(date,order_date,105) BETWEEN cast('22/04/2015' as Date) AND cast('04/05/2015' as date)

Upvotes: 1

Related Questions