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.
When writing dates and times in T-SQL we need to surround the date using single quotes. This is because SQL can not 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 13 rows are returned. We are using an equal (=) sign to specify that the date has to 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 common 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 and AND statements. Using the BETWEEN and 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 exactly the same. The proper way to write it is up to the person writing the statement.