Where Date in SQL Reference Guide and Examples (2024)

By: Joe Gavin |Updated: 2023-01-19 |Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | More > TSQL

Problem

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

Solution

This tip looks at several SQL query examples of the WHERE clause withDATES 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 theTimestamp column for all of the examples.

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, December13, 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 thetimestamp.

To get the oldest record, use the MIN function:

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

To get the newest record, use the MAX function:

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

Greater Than or Equal to a Date

Let's look for recordsthat 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 beginningof 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 recordsthat 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 beginningof 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 clausestring 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 timestampto the filter with an AND.

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

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

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 theendpoints 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 theDATEADD andGETDATESQLdate functions to look for a period preceding thecurrent 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 datepartsto look back, andGETDATE with no additional parameters.

Syntax: DATEPART (datepart, date)

The first example uses 'yy' for the datepart and will return recordsfor 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 statementand 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 theDATEADDSQL datefunction with a datepart, to indicatehow 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 olderthan a certain amount of time.

Here we'll set the Archive field to 1 for recordsolder than 7 days:

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

Next Steps

We've seen simple examples using the WHERE clause with dates and timesin SQL Server.

Here are some additional tips:

• SQL WHERE Tutorial
• SELECT with WHERE and ORDER BY
• SQL WHERE Clause Explained
• SQL BETWEEN Operator for WHERE Clause, CASE, INSERT, DELETE and UPDATE statements
• Date and Time Conversions Using SQL Server
• How to Get Current Date in SQL Server
• Add and Subtract Dates using DATEADD in SQL Server
• SQL Server Date Time Calculation Examples
• Date and Time Conversions Using SQL Server
• Date Format in SQL Server with FORMAT Function
• SQL Convert Date to YYYY-MM-DD
• DATEDIFF SQL Server Function

About the author

Article Last Updated: 2023-01-19