563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Dates and Times in SQL Server
Part three of a six-part series of blogs
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!
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.
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.
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.
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.
|Parts of this blog|
25 Aytoun Street