WHERE: Using Functions On Dates

In our last module, we were trying to find the users who signed up on one particular date. We ran into problems because SQL Server stores both dates and times in DATETIME fields.

But what if we just want the DATE part of a DATETIME field?

Enter the CONVERT function.

You can convert datatypes from one to another using the CONVERT function:

Run that query, and the results are magical:

Date comparisons now work
Date comparisons now work

In our last lesson, the equality didn’t work because the CreationDate field was a DATETIME field that included both dates and times, but when we convert it to just be a date field, then equality comparisons can work.

Most of the time, SQL Server does a good job of converting stuff for you without you having to know its datatype, but here it comes in handy when we just need to check the date.

Say you wanted to find everybody who signed up at 4:20PM, regardless of the day – you could convert that DATETIME field to just a TIME instead:

Run that query, and as of this writing, it’s just one lonely guy with a reputation of 1:

Come back, mohammed52, we miss you
Come back, mohammed52, we miss you

SQL Server has lots of date functions that work magic on dates.

The DATEPART function gives you months, days, hours, and more.

If you wanted to find everyone who signed up in November, or in the midnight hour, here’s how you could do it:

Note the syntax on that last one – we could have said DATEPART(YEAR, CreationDate), but we used a little bit of a shortcut. There’s a huge list of date functions, which means there’s shortcuts for stuff like YEAR or MONTH.

Now, you try.

Go to the home page of StackOverflow.com, then click on the Month tab. Write a query to return the list of posts created this month. Since both the questions and answers live in the Posts table, your query will return both questions and answers – but we’ll teach you how to filter that down soon.

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