Where Date in SQL Reference Guide and Examples

Problem

Filtering on dates and times is a common SQL operation. This tutorial provides a simple, helpful reference for using the WHERE clause with dates and times in Microsoft SQL Server.

Solution

This tip looks at several SQL query examples of the WHERE clause with DATES that can be copied, pasted, and edited for your use in a SQL database.

The T-SQL examples are broken into the following categories:

Find oldest record based on Date / Time
Find newest record based on Date / Time
Find records less than or equal to a Date / Time
Find records greater than or equal to a Date / Time
Find records in a range of Dates / Times
Delete records based on Date / Time
Update records based on Date / Time

Sample Table and Queries

A simple table called errorlog has been created and populated with sample data.

The table has the following four fields and data types. We will be using the Timestamp column for all of the examples.

Column Name	Data Type
Severity	nvarchar(50)
Timestamp	datetime2(3)
Message	nvarchar(max)
Archive	bit

Here are the top 10 records from the table:

SELECT TOP (10) [Severity]
               ,[Timestamp]
               ,[Message]
               ,[Archive]
FROM [dbo].[errorlog];

The data range for the dataset is from Friday, December 09, 2022, 06:01 AM to Tuesday, December 13, 2022, 02:16 PM.

Find Oldest and Newest Records

First thing I want to see is how old and how current the data is based on the timestamp.

To get the oldest record, use the MIN function:

-- get earliest timestamp
SELECT MIN([Timestamp])
FROM [dbo].[errorlog];

To get the newest record, use the MAX function:

-- get latest timestamp
SELECT MAX([Timestamp])
FROM [dbo].[errorlog];

Greater Than or Equal to a Date

Let’s look for records that are greater than 12/13/22::

SELECT  [Severity]       
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] > '2022-12-13';

Add the equal sign (=) to change the filter to greater than or equal to the beginning of 12/13/22:

SELECT  [Severity]
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WWHERE [Timestamp] >= '2022-12-13';

Less Than or Equal to a Date

Now, let’s look for records that are less than 12/13/22:

SELECT  [Severity]]       
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] < '2022-12-13';

Same as the earlier example, add the equal sign (=) to change the filter to less than or equal to the beginning of 12/13/22:

SELECT  [Severity]
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WWHERE [Timestamp] <= '2022-12-13';

Greater Than or Equal to a Date / Time

So far, we’ve filtered only on the date. Add the time to the WHERE clause string to further filter down to the time.

This will look for records greater than 12/12/22 02:15 PM:

SELECT  [Severity]       
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] > '2022-12-13 14:15';

Greater than or equal to 12/12/22 02:15 PM:

SELECT  [Severity]
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHWHERE [Timestamp] >= '2022-12-13 14:15';

Less Than or Equal to a Date / Time

Less than 12/12/22 02:15 PM:

SELECT  [Severity]       
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] < '2022-12-13 14:15';

Less than or equal to 12/12/22 02:15 PM:

SELECT  [Severity]
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] <= '2022-12-13 14:15';

Range of Dates / Times

We can search for records in a specific timeframe by adding the ending timestamp to the filter with an AND.

Here is one way to look for records that are greater than 12/13/22 11:00 AM and less than 12/13/22 02:00 PM:

SELECT  [Severity]       
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] > '2022-12-13 11:00' AND [Timestamp] < '2022-12-13 14:00';

Here we include the endpoints, so we also include the starting and ending times:

SELECT  [Severity]       
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] >= '2022-12-13 11:00' AND [Timestamp] <= '2022-12-13 14:00';

A simpler way to do the above is to use BETWEEN. This will include the endpoints and is equivalent to using >= and < =

SELECT  [Severity]       
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] BETWEEN '2022-12-13 11:00' AND '2022-12-13 14:00';

Filtering on a Period Prior to the Current Date and Time

We can use the DATEADD and GETDATE SQL date functions to look for a period preceding the current date and time. We do this by specifying a datepart to determine the period, as shown in the table below. A negative number indicates a number of dateparts to look back, and GETDATE with no additional parameters.

Syntax: DATEPART (datepart, date)

Datepart	Abbreviation(s)
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	Hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

The first example uses ‘yy’ for the datepart and will return records for the past year:

SELECT  [Severity]
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] > DATEADD(yy,-1,GETDATE());

The use of ‘qq’ returns the last quarter:

SELECT  [Severity]
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] > DATEADD(qq,-1,GETDATE());

The use of ‘mm’ returns the last month:

SELECT  [Severity]
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] > DATEADD(mm,-1,GETDATE());

The use of ‘dd’ returns the last day:

SELECT  [Severity]
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] > DATEADD(dd,-1,GETDATE());

The use of ‘hh’ returns the last hour:

SELECT  [Severity]
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] > DATEADD(hh,-1,GETDATE());

The use of ‘mm’ returns the last minute.

SELECT  [Severity]
       ,[Timestamp]
       ,[Message]
       ,[Archive]
FROM [dbo].[errorlog]
WHERE [Timestamp] > DATEADD(mi,-1,GETDATE());

We can do all of the same things we saw in the above examples such as <, <=, >, >=, and BETWEEN.

Deleting Records Based on Date / Time

Deleting records based on a specific date is accomplished with a DELETE statement and date or date / time in the WHERE clause.

This example will delete all records before 12/10/22:

DELETE [dbo].[errorlog]
WHERE [Timestamp] < '2022-12-10';

We can also use the DATEADD SQL date function with a datepart, to indicate how many dateparts to look back or forward along with GETDATE().

This deletes records older than 7 days:

DELETE [dbo].[errorlog]
WHERE [Timestamp] < DATEADD(dd,-7,GETDATE());

Updating Records Based on Date / Time

We may want to flag records in a table to be archived if they’re older than a certain amount of time.

Here we’ll set the Archive field to 1 for records older than 7 days:

UPDATE [dbo].[errorlog]
SET [Archive] = 1
WHERE [Timestamp] < DATEADD(dd,-7,GETDATE());

Next Steps

We’ve seen simple examples using the WHERE clause with dates and times in SQL Server.

Here are some additional tips:

Resources

Community

Subscribe