This page has 0 threads | Add post

Dates and Times in Criteria

Written by Andrew Gould

In this tutorial

Using Dates and Times in Criteria
Some Date Formats Don't Always Work
Searching in a Range of Dates
What's Next?

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.

What's Next?

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.

Previous
tutorial

Calculations and Expressions

Previous
part

Date and Time Data Types in SQL Server

Top of page

Next
part

Formatting Dates and Times in SQL Server

Next
tutorial

Joins

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Dates and times

Dates and Times in Criteria

Using Dates and Times in Criteria

Some Date Formats Don't Always Work

Searching in a Range of Dates

What's Next?

Head office

London

Manchester