Query SQL Server Data Based on Various Date and Time Functions

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


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];
Top 10 Records

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];
Earliest and Latest Timestamps

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:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Joe Gavin Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-01-19

Comments For This Article




Wednesday, July 3, 2024 - 4:41:57 PM - Jeff Moden Back To Top (92356)
Either I mistook some code in my previous comment or something has been changed. Probably the former.

The proper Closed/Open method is NOT being used in this article and that's a problem.

Friday, January 20, 2023 - 11:19:06 AM - Jeff Moden Back To Top (90838)
Closed/Open method instead of (ugh!) BETWEEN. Totally SARGable predicates in the WHERE clause. Nicely done, Joe!














get free sql tips
agree to terms