BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Dates and times can be the most frustrating data types to work with in SQL Server. Learn everything you need to know about the way dates work with this handy blog series and enjoy happier times!
- Dates and Times in SQL
- Date and Time Data Types in SQL Server
- Dates and Times in Criteria (this blog)
- Formatting Dates and Times in SQL Server
- Calculations with Dates in SQL Server
- How to Calculate Age in SQL Server
Posted by Andrew Gould on 13 February 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Dates and Times in Criteria
Now that you know a bit about how dates and times are entered and saved in a SQL Server database you're ready to learn how to write criteria in queries involving dates.
Using Dates and Times in Criteria
To use a date and/or time in the criteria of a query you simply need to enter it enclosed in a set of single quotes. The example below shows any events that happened on the 1st of January, 2000.
Notice that we've used the ISO 8601 standard date format.
We've used the international standard date format that we mentioned in the previous part of this blog series, but it's worth mentioning that a large number of other date formats are accepted in criteria.
All of these date formats would work to show events that happened on the same date.
Some Date Formats Don't Always Work
Just to highlight why it's worth always using the international standard "YYYY-MM-DD" date format, here are some examples of when other date formats don't always give you the results you expect.
Here we've tried to show events that occurred on the 1st of July, 2007 and returned one result.
If we try to do the same thing using the standard UK "DD/MM/YYYY" format we get slightly different results:
Here we don't find any results at all.
We're even less successful when we try to find things that happened on the 25th of October, 2007 using the UK standard format:
This time we see an error message informing us that we're using an "out-of-range datetime value".
The problem in both of the examples shown above is that the server hasn't been localised to use UK regional settings and so is interpreting our date criteria in "MM/DD/YYYY" format. It is possible to tell the query to use a different format, like so:
You can use the SET DATEFORMAT statement to change the default date format for a single query.
We still think it's more sensible to always use the "YYYY-MM-DD" format when working with dates as it's internationally recognised, unambiguous and unaffected by the SET DATEFORMAT statement.
Searching in a Range of Dates
As well as searching for something which happened on a specific date you can also look for records which occur before or after a certain date, or which fall within a date range. This works in exactly the same way as it does for numbers.
To show things that happened on or after a certain date, use the "greater than or equal to" operators. You can use the "less than" operator to find things which happened before a certain date.
There are two main ways to test if a date falls within a certain range:
This shows everything that happened in the year 2000.
Alternatively you can use the slightly simpler syntax shown below:
Use BETWEEN to check for dates that fall within a certain range.
So far we've worked with dates in the criteria of queries. Whichever format you choose to write your dates in, you'll always see them output in "YYYY-MM-DD" format. In the next article in this series we'll show you how to change the format of dates in the output of your queries, including how to build elaborate custom formats.