Using Criteria in SQL Queries
Part four of a six-part series of blogs

When you write queries in SQL it's immensely useful to be able to show records matching criteria that you've set. You can do this using the WHERE clause and this blog teaches you how to use it!

  1. Using Criteria in SQL Queries
  2. Criteria Using Numbers
  3. Criteria Using Text
  4. Date Criteria in SQL Server (this blog)
  5. Using AND, OR and NOT in SQL Server Queries
  6. Using NULL in Criteria

This blog is part of our online SQL tutorial blog - however, Wise Owl also run SQL courses for up to 6 people.

Posted by Andrew Gould on 16 January 2013

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.

Date Criteria in SQL Server

I've previously written an article on working with dates and times in SQL Server which includes a section on using dates in criteria.  In this blog I'll just summarise the main points.

Dates are Entered as Text

When you enter a date in a query in SQL you must enclose the date in a set of single quotes, as shown below:

SELECT

FilmName

,FilmReleaseDate

FROM

tblFilm

WHERE

FilmReleaseDate = '01/01/1999'

The above query would find any films released on January 1st 1999; the results are shown below:

Simple date

Only one film in our database was released on that date.

 

Without the quotes around the date the query would search for any film whose release date was 1 divided by 1 divided by 1999 and so wouldn't return any results.

The Best Format to Use

You can enter dates in a variety of formats but the way those formats are interpreted is dependent on the localisation settings of your server.  To avoid confusion between, for example, US and UK date formats you should always enter your dates in the format yyyy-mm-dd, as in the example shown below:

SELECT

FilmName

,FilmReleaseDate

FROM

tblFilm

WHERE

FilmReleaseDate = '1996-02-23'

The query will show any films whose release date is February 23rd 1996 and the results are shown below:

Proper date format

Two of them are worth watching at least...

 

For anyone who's interested, the date format used above conforms to the ISO 8601 international standard for representing dates!

Dates Behave Like Numbers

You can use many of the same criteria with dates as with numbers, such as the greater than and less than operators.  The example below finds all of the films released since January 1st 2000:

SELECT

FilmName

,FilmReleaseDate

FROM

tblFilm

WHERE

FilmReleaseDate >= '2000-01-01'

The query searches for all of the films whose release date is greater than or equal to the specified date.  The results are shown below:

Films from 2000 onwards

This is just a small selection of the results returned by the query.

 

You can also find records that fall within a range of dates using the BETWEEN operator, as shown below:

SELECT

FilmName

,FilmReleaseDate

FROM

tblFilm

WHERE

FilmReleaseDate BETWEEN '2000-01-01' AND '2000-12-31'

The above query will show all of the films released between the first and last days of the year 2000.  The results are shown below:

Dates in a range

The release date of all of these films falls within the specified range.

 

Searching for Parts of a Date

Sometimes it's convenient to search for parts of a date, such as in the above example.  Rather than looking for dates which fall within the specified range we could just ask to see records where the year of the film is equal to 2000.  In order to do this we have to first calculate the year of the release date using a function:

SELECT

FilmName

,FilmReleaseDate

FROM

tblFilm

WHERE

YEAR(FilmReleaseDate) = 2000

The YEAR function calculates the year portion of a date and returns the answer as a number.  The results of this query are the same as for the example shown above which uses the BETWEEN operator.

You can also use the MONTH and DAY functions to calculate the other relevant parts of a date.  Each of these functions returns an answer in the form of a number.  The example below would find all of the films released in the month of May:

SELECT

FilmName

,FilmReleaseDate

FROM

tblFilm

WHERE

MONTH(FilmReleaseDate) = 5

The example below would find all of the films released on the tenth day of the month:

SELECT

FilmName

,FilmReleaseDate

FROM

tblFilm

WHERE

DAY(FilmReleaseDate) = 10

You can find out more on extracting parts of a date by reading this blog on formatting dates in SQL.

What's Next?

Now that we've covered all of the main data types it's time to find out how to combine criteria using the logical operators.  Read on to find out how!

This blog has 0 threads Add post