In a previous post, we discussed how to write a select statement. Over the next several posts we will discuss how to use the WHERE keyword to filter records by numbers, character data, dates and times, and NULL values. This post specifically will focus on filtering records by dates and times.

SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate = '06/01/2012'

When writing dates and times in T-SQL we need to surround the date using single quotes. This is because SQL cannot recognize dates unless we use single quotes, otherwise, SQL will think it is a math statement. In the code above, if we did not use the single quotes, SQL would try to divide 1 by 6 and then divide that by 2012. This would return a value of less than 0 and SQL would try to find records that were added on January 1st, 1900.

To filter our records from the Sales.SalesOrderHeader table, we are using the WHERE keyword to return orders that were placed on June 1st, 2012, and thirteen rows are returned. We are using an equal (=) sign to specify that the date must be exactly the date specified.

We can specify the date in any format, in our example, we are using the United States standard of MM/DD/YYYY. However, it is customary practice to use the international standard of YYYY-MM-DD HH:MM:SS.ms . (More specifically, use the largest date value down to the smallest date value.)

There are two ways of selecting a range of dates we can use either the greater than (>) or less than (<) operators or the BETWEEN with the AND statement. Using the BETWEEN with the AND statements are inclusive of the first date and the last date just as if I used the greater than or equal (>=) sign or the less than or equal (<=) sign. Both statements below will return the same 390 records and are executed the same. The proper way to write it is up to the person writing the statement.

--Searching for Date Ranges with equality operators
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '06/01/2012' AND
	  OrderDate <= '06/30/2012/'

--Searching for Date Ranges using the BETWEEN/AND statements
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '06/01/2012' AND '06/30/2012/'