This page has 0 threads | Add post

Date Criteria in SQL Server

Written by Andrew Gould

In this tutorial

Dates are Entered as Text
The Best Format to Use
Dates Behave Like Numbers
Searching for Parts of a Date
What's Next?

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 tutorial 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:

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:

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:

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:

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!

Previous
tutorial

Writing queries

Previous
part

Criteria Using Text

Top of page

Next
part

Using AND, OR and NOT in SQL Server Queries

Next
tutorial

WHERE criteria (version B)

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

WHERE criteria (version A)

Date Criteria in SQL Server

Dates are Entered as Text

The Best Format to Use

Dates Behave Like Numbers

Searching for Parts of a Date

What's Next?

Head office

London

Manchester