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
– cardmagik, Commented Feb 25, 2013 at 23:17
What type is b.BirthDate? Is it date, datetime, or something else? — mikurski
– mikurski, Commented Feb 25, 2013 at 23:20
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
– Clockwork-Muse, Commented Feb 26, 2013 at 0:09

Indi · Accepted Answer · 2013-02-25 23:18:46Z

8

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')

answered Feb 25, 2013 at 23:18

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GarethD Over a year ago

I'd give another upvote if I could for using a dateformat that is not culture specific too.

Kenny Yu · Accepted Answer · 2013-02-25 23:18:07Z

2

I think you are comparing the string instead of date time, so the results have the records '02/25' & '02/17'.

answered Feb 25, 2013 at 23:18

Kenny Yu

335 bronze badges

1 Comment

Kenny Yu Over a year ago

remove all "CONVERT" from your "where" clause.

Jmyster · Accepted Answer · 2013-02-25 23:30:28Z

1

Assuming that your birthdate field is a DateTime type you need to convert you inputs to the same type.

Did you try?

WHERE (b.BirthDate BETWEEN Cast('02/01/2013' As DateTime) and Cast('02/27/2013' As DateTime))

edited Feb 25, 2013 at 23:30

answered Feb 25, 2013 at 23:22

Jmyster

1,0032 gold badges10 silver badges28 bronze badges

3 Comments

Aaron Bertrand Over a year ago

Dangerous. What if I'm in the UK? Now it's looking for January 2 instead of February 1.

Jmyster Over a year ago

The dates where the OP's dates. Am I incorrect that the Cast is culture respective? If the OP was in the UK, the date parameters would be different not the statement (01/02/2013 and 27/02/2013)? Just asking

Aaron Bertrand Over a year ago

My point is that encouraging regional formats (even if the OP introduced them) is a bad idea. The reason? The next reader of your answer might not have the same requirements as the OP, and may be looking for 2/1 -> 2/8, so the "obvious" format in your answer might be totally missed. Please read sqlblog.org/2009/10/16/… and sqlblog.org/2011/10/19/…

Collectives™ on Stack Overflow

SQL query select between two dates error

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related