SQL query select between two dates error

Question

I am in need of your help as I am struggling with a query in SQL. This query is not giving exact result that I want. I need to pull out all the dates between two dates.

I tried two queries:

SELECT a.MemberID,CONVERT(varchar(12), b.BirthDate, 101) AS BirthDate
FROM Members_Hired AS a
    INNER JOIN Members AS b
        ON a.ReferenceID = b.RecordID
WHERE (CONVERT(varchar(12), b.BirthDate, 101) >= '02/01/2013'
    and CONVERT(varchar(12), b.BirthDate, 101) <= '02/27/2013')

and

SELECT a.MemberID,CONVERT(varchar(12), b.BirthDate, 101) AS BirthDate
FROM Members_Hired AS a
    INNER JOIN Members AS b
        ON a.ReferenceID = b.RecordID
WHERE (CONVERT(varchar(12), b.BirthDate, 101) BETWEEN '02/01/2013' and '02/27/2013')

This gives results with the date '02/05/1990', '02/17/1990' also. It considers only month and date, not the year exactly.

If you are comparing dates, you have to reverse it so yyyy comes first, then mm, then dd. If birthdate is a date field, you could potentially convert it using to_char(b.birthdate,'yyyy-mm-dd') - that would be Oracle syntax though which is why I'm posting this as a comment and not an answer — cardmagik
What type is b.BirthDate? Is it date, datetime, or something else? — mikurski
While the answers given are right about using date/time types for comparisons, they all miss one thing - they shouldn't be using BETWEEN (especially on SQL Server). Anything else is the wrong way to think of time... — Clockwork-Muse

Kaf Kaf · Accepted Answer · 2013-02-25T23:18:46

You should compare dates not varchar values. Change your where clause as below. Also note that I am using ISO format (yyyymmdd) for string dates before converting to make sure it works in any culture.

b.birthdate between convert(date,'20130201' ) and 
                  convert(date, '20130227')

SQL query select between two dates error

3 Answers