How do I use the SQL WHERE clause with dates?
Using the WHERE clause with dates is very similar to filtering text. Date values must be enclosed with the single apostrophe. To find all rows that have an OrderDate equal to July 15, 2005 in the Sales.SalesOrderHeader table, we use the query:
WHERE OrderDate = '7/15/2005'
We can use different comparison operators with date values as well. To find all orders that took place after July 15, 2005 we can use the query:
WHERE OrderDate > '7/15/2005'
While the previous examples have used the slash symbol to separate the month, day, and year, you can also use the hyphen to separate the values:
WHERE OrderDate > '7-15-2005'
Just like with the other examples of the SQL WHERE clause, we can combine multiple filtering conditions using the “AND” or “OR” operator. To find all rows where the OrderDate was either July 15, 2005 or July 16, 2005 then we can execute the query:
WHERE OrderDate = '7/15/2005' OR OrderDate = '7/16/2005'
There are many date functions that can be used in a WHERE clause that give you a little more flexibility with your queries. An upcoming post will discuss these various date functions. As we get to later lessons, we will apply some of those to give you dynamic options.
Given the examples completed in this lesson, try and complete some of the SQL WHERE clause practice problems below!
- Find all orders that took place before August 1, 2006 using the OrderDate column and the Sales.SalesorderHeader table.
- Find all rows in the HumanResources.Employee table with a BirthDate after September 12, 1970
- Find all orders in the Sales.SalesOrderHeader table with an OrderDate value that was after January 3, 2006 and before July 8, 2006.