The Space BETWEEN

Thanks to Robert for his comment on the last post that then spawned this post. In the example about sargable dates, I thought I would go with the more simple look and only use dates instead of adding the times. The point is to look at sargability, right? Well, here’s an example on why you don’t mix and match dates and datetimes.

From the Top

The original query:

SELECT DisplayName, CreationDate
FROM Users
WHERE CreationDate BETWEEN '2013-01-01' AND '2014-01-01'
ORDER BY CreationDate;
GO

This appears to give us the intended result of users created in 2013. If we use the MIN() and MAX() functions we see the following:

SELECT MIN(CreationDate) AS 'Min_CreationDate', MAX(CREATIONDATE) AS 'Max_CreationDate'
FROM Users
WHERE CreationDate BETWEEN '2013-01-01' AND '2014-01-01';
GO

The min date is 7 seconds into 2013 and the max date is a few minutes before 2014.

The Ol’ Switcheroo

If we look at the execution plan:

We’ll notice something about the WHERE clause. The BETWEEN from our query is switched to:

Seeing the >= and <= in relation to the data we want should set off some red flags.

The StackOverflow2013 data I’m tested with had no 2014 users. Let’s pick a random user and change that for the sake of our testing:

UPDATE Users
SET CreationDate = '2014-01-01'
WHERE Id = 3150400;
GO

I’m purposely leaving off the time. SQL assumes the time and fills that in for us as shown below:

Now What?

What happens if we run our MIN/MAX query to show users created in 2013?

SELECT MIN(CreationDate) AS 'Min_CreationDate', MAX(CREATIONDATE) AS 'Max_CreationDate'
FROM Users
WHERE CreationDate BETWEEN '2013-01-01' AND '2014-01-01';
GO

We’re looking for 2013 but now a 2014 snuck into our results.

That means with this query, we can be looking for dates in 2013 but still get a date in 2014. Another way to visualize what SQL is running is:

SELECT MIN(CreationDate) AS 'Min_CreationDate', MAX(CREATIONDATE) AS 'Max_CreationDate'
FROM Users
WHERE CreationDate >= '2013-01-01 00:00:00' AND CreationDate <= '2014-01-01 00:00:00';
GO

We can change our <= to < which will get us what we want:

SELECT MIN(CreationDate) AS 'Min_CreationDate', MAX(CREATIONDATE) AS 'Max_CreationDate'
FROM Users
WHERE CreationDate >= '2013-01-01 00:00:00' AND CreationDate < '2014-01-01 00:00:00';
GO

You could also still use BETWEEN and be specific as you need to about the times.

The lesson? Don’t mix and match date and datetime and don’t make something more complicated by simplifying it.

Happy Thanksgiving!