The query was perfectly broken…

I have been teaching a T-SQL 101 class and for the homework, we asked the students to get all the records where our heroes had a birthdate between 1995 through 1999. I expected something like this:

SELECT FirstName, LastName, Birthdate
FROM Heroes
WHERE Birthdate BETWEEN '1/1/1995' AND '12/31/1999'


SELECT FirstName, LastName, Birthdate
FROM Heroes
WHERE Birthdate >= '1/1/1995' AND Birthdate <= '12/31/1999'

Imagine my surprise when one of the students turned in this:

SELECT FirstName, LastName, Birthdate
FROM Heroes
WHERE Birthdate BETWEEN '1995' AND '1999'

When I first saw the query I thought, “There is no way they ran that and it worked.” So I wrote it up and ran it on my data. Guess what? IT RUNS AND RETURNS DATA! I was shocked.

I started looking at the plan and what it did to the data and found that it had done an implicit conversion on the dates and assumed 1/1/1995 to 1/1/1999 based on the year. So we were missing data from the results, but I was still in shock that it had run in the first place and shared this information with my co-worker who reminded me that dates get crazy and if I only put in ’12/31/1999′ and there is a time in the field, it will cut off most of the times within that day because it will assume I want ’12/31/1999 00:00:00′. If I want the full day, I need to get in the habit of specifying ’12/31/1999 23:59:59′ or ‘1/1/2000 00:00:00’ and then test my results to make sure I am getting what I truly want back from the database.

The song for this post is BANNERS – Perfectly Broken.

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

17 thoughts on “The query was perfectly broken…

  1. Edward Miller says:

    SELECT FirstName, LastName, Birthdate
    FROM Heroes
    WHERE DATEPART(year, Birthdate) BETWEEN 1995 AND 1999

  2. DW says:

    SELECT FirstName, LastName, Birthdate
    FROM Heroes
    WHERE CONVERT(char(8),Birthdate,112) BETWEEN ‘1995’ AND ‘1999’

  3. Curtis Gregson says:

    But that of course is a non-sargable query and should be avoided. The SQL optimizer wouldn’t be able to use an index on Birthdate, even if one exists. It will have to evaluate the DATEPART() function for every row of the table. The Where Birthdate >= ## and Birthdate <=## is much better.

  4. Jack Barlow says:

    Even better if the column includes time:

    WHERE BirthDateTime >= ‘1995-01-01’ AND BirthDateTime < '2000-01-01'

  5. I never use between with dates because of everything above. I commonly need to select month ranges and it is just simpler to use >= (some date) and < first of the next month which also eliminates making a mistake on how many days are in the last month in the range. Teaching a beginner user \this method eliminates everything that can go wrong using between.

  6. Jeff Moden says:

    IMHO, the proper way to handle dates is to make them bullet-proof for time no matter what the resolution for the time is. In other words, always use the Closed/Open method of SomeDtCol >= SomeInclusiveStartDate and SomeDtCol = ‘1995’ AND BirthDateTime < '2000'

    On a similar note, I'm really disappointed that MS came out with an EOMonth() function but not an FOMonth() function, which would make Closed/Open criteria much easier.

    I also don't understand why the came out with a DATEDIFF_BIG() function but no DATEADD_BIG() function. It make milli-second UNIX time conversions a bit more painful for people and they usually get stuck with something that will hit the wall in 2038 due to the implicit conversions to DATETIME().


  7. Robin Wilson says:

    Is this not a good way of doing it?


    FROM Heroes H
    CAST ( Birthdate AS DATE ) >= ‘1995-01-01’
    AND CAST ( Birthdate AS DATE ) <= '1999-12-31'

    That way the time element is eliminated.
    I believe dates in the format 19991231 are universal so avoid the need for the dateformat but I find that less clear so avoid it.

    • JeffModen says:

      MS claims that particular conversion is SARGable and a good friend of mine proved that is is. Unfortunately, it’s still a fair bit slow because it’s doing a conversion on all rows it touches instead of doing the conversion just on the string literals, in this case.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s