This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Using Criteria in SQL Queries Criteria Using Numbers Criteria Using Text Date Criteria in SQL Server (this blog) Using AND, OR and NOT in SQL Server Queries 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.

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!

Using Criteria in SQL Queries
Criteria Using Numbers
Criteria Using Text
Date Criteria in SQL Server (this blog)
Using AND, OR and NOT in SQL Server Queries
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

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:

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!

Parts of this blog
Using Criteria in SQL Queries Criteria Using Numbers Criteria Using Text Date Criteria in SQL Server (this blog) Using AND, OR and NOT in SQL Server Queries Using NULL in Criteria

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

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