WHERE: Filtering Dates

The Tags table has been pretty useful to us so far, but there’s no dates on it. We’re going to have to switch over to the other table you’ve been working with, Users.

StackOverflow Example of Filtering Dates

At the top of StackOverflow, click Users, then at the top, click the New Users tab, then Creation Date. You’ll arrive here:

By now, as you’ve been using the dbo.Users table for your homework exercises, you may have noticed a CreationDate field:

SELECT * FROM dbo.Users;

1	SELECT * FROM dbo.Users;

When you run that query, you get:

stackoverflow-users — All the lovely Users

That CreationDate doesn’t refer to their birthday, but rather the date when their Stack account was created. If you want to filter for just the accounts that were created recently, use a date filter in your WHERE clause:

SELECT * FROM dbo.Users WHERE CreationDate &gt; '2016/06/01';

1	SELECT * FROM dbo.Users WHERE CreationDate > '2016/06/01';

Run that query, and you get:

Just like we did with numbers, you can use ranges and equality operators:

SELECT * FROM dbo.Users WHERE CreationDate &gt;= '2016/01/01';
SELECT * FROM dbo.Users WHERE CreationDate BETWEEN '2016/01/01' AND '2016/12/31';
SELECT * FROM dbo.Users WHERE CreationDate = '2016/04/01'

SELECT * FROM dbo.Users WHERE CreationDate >= '2016/01/01';

SELECT * FROM dbo.Users WHERE CreationDate BETWEEN '2016/01/01' AND '2016/12/31';

SELECT * FROM dbo.Users WHERE CreationDate = '2016/04/01'

Except there’s a very big catch.

Datetime fields also include times, so equality is tricky.

If you run this query:

SELECT * FROM dbo.Users WHERE CreationDate = '2016/04/01';

1	SELECT * FROM dbo.Users WHERE CreationDate = '2016/04/01';

You might expect to get all users created on the day of 2016/04/01 – but that’s not the case. You get:

Because when you say = ‘2016/04/01’, what you’re really saying is ‘2016/04/01 00:00:00’ – midnight. Instead, if you want people who were created all day on the 1st of April, you’ll need to do something like:

SELECT * FROM dbo.Users WHERE CreationDate BETWEEN '2016/04/01 00:00:00' AND '2016/04/01 23:59:59';

1	SELECT * FROM dbo.Users WHERE CreationDate BETWEEN '2016/04/01 00:00:00' AND '2016/04/01 23:59:59';

And when you run that query, you get:

Much better! Except is that really right? Not necessarily – because I only said 23 hours, 59 minutes, and 59 seconds – but there’s almost a full second before the next midnight strikes again. All kinds of milliseconds.

Diabolical.

What we really need to do is convert the date/time combo field into just the date, and only examine the date part of it. We’ll tackle that in the next lesson.

Now, you try.

Go to the home page of StackOverflow.com, and you’ll see a list of newly created questions. Questions (and answers) are stored in the Posts table.

Write a query to show the posts that were created this month.

When you’re done with that, move on to the next lesson.