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'
OR
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.
Andrea Allred presents RoyalSQL 
[…] Andrea Allred gets tested: […]
SELECT FirstName, LastName, Birthdate
FROM Heroes
WHERE DATEPART(year, Birthdate) BETWEEN 1995 AND 1999
That would absolutely work correctly. Great Job!
Terrible code as this will always do a table scan due to the function on a table column. DBA nightmare.
SELECT FirstName, LastName, Birthdate
FROM Heroes
WHERE CONVERT(char(8),Birthdate,112) BETWEEN ‘1995’ AND ‘1999’
This query has the same problem as the first one I highlighted, it will not show anything from 1999. The requirement was to include 1999.
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.
You are correct! Great call out!
Even better if the column includes time:
WHERE BirthDateTime >= ‘1995-01-01’ AND BirthDateTime < '2000-01-01'
Yes! I love this! I did end of teaching this part in my class because time can bite for sure. Thank you!
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.
Which is super smart. I am teaching them the good and bad and showing them why bad things are bad so they can avoid them as they build their careers.
+1000 😀
Right? When new stuff is released, I usually have a lot of questions like that. Thank you!
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().
😀
Is this not a good way of doing it?
SET DATEFORMAT ymd
SELECT
H.FirstName,
H.LastName,
H.Birthdate
FROM Heroes H
WHERE
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.
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.