[sql] Query to select data between two dates with the format m/d/yyyy

I am facing problem when i'm trying to select records from a table between two dates.

m using the following query

select * from xxx where dates between '10/10/2012' and '10/12/2012'

this query works for me but when the dates are in format like 1/1/2013.. it doesn't work..

plz solve my problem ASAP.

This question is related to sql database sql-server-2008

The answer is


This solution provides CONVERT_IMPLICIT operation for your condition in predicate

SELECT * 
FROM xxx 
WHERE CAST(dates AS date) BETWEEN '1/1/2013' and '1/2/2013'

enter image description here

OR

SELECT * 
FROM xxx 
WHERE CONVERT(date, dates, 101) BETWEEN '1/1/2013' and '1/2/2013'

enter image description here

Demo on SQLFiddle


Similar questions with sql tag:

Similar questions with database tag:

Similar questions with sql-server-2008 tag: