While SQL Server’s DATEDIFF() function is relatively straightforward when you’re comparing two known dates, it becomes more flexible when the dates you compare are sourced directly from your tables. Instead of hardcoding dates, you can embed subqueries directly into the DATEDIFF() function to dynamically retrieve the dates you need.

This approach can be especially useful when you’re working with aggregate functions like MIN() and MAX(), or when you need to pull specific dates based on certain conditions. The subqueries execute first, return their date values, and DATEDIFF() uses those results to perform the calculation.

Example

Let’s say you’re working with a weather station database that records daily temperature observations. You want to know the total span of your observation period without manually looking up the first and last dates.

For this example, we’ll use the following table and data:

-- Create the weather observations table
CREATE TABLE TemperatureReadings (
    ReadingID INT PRIMARY KEY IDENTITY(1,1),
    StationCode VARCHAR(10),
    ObservationDate DATE,
    MaxTempCelsius DECIMAL(5,2),
    MinTempCelsius DECIMAL(5,2)
);

-- Insert sample data spanning several months across multiple stations
INSERT INTO TemperatureReadings (StationCode, ObservationDate, MaxTempCelsius, MinTempCelsius)
VALUES 
    ('WX-042', '2025-03-15', 18.5, 7.2),
    ('WX-042', '2025-03-16', 19.3, 8.1),
    ('WX-042', '2025-04-22', 22.7, 11.4),
    ('WX-042', '2025-05-08', 25.1, 13.8),
    ('WX-042', '2025-06-14', 28.9, 16.2),
    ('WX-042', '2025-07-03', 31.4, 19.5),
    ('WX-042', '2025-08-20', 29.8, 18.3),
    ('WX-042', '2025-09-11', 24.6, 14.7),
    ('WX-118', '2025-01-10', 12.3, 2.8),
    ('WX-118', '2025-02-05', 14.7, 4.1),
    ('WX-118', '2025-03-28', 19.8, 8.9),
    ('WX-118', '2025-05-15', 24.2, 12.6),
    ('WX-118', '2025-10-02', 21.5, 11.3),
    ('WX-227', '2025-04-01', 16.9, 6.5),
    ('WX-227', '2025-06-20', 27.4, 15.8),
    ('WX-227', '2025-07-18', 30.2, 18.9);

Now let’s use the DATEDIFF() function with subqueries to determine the total observation period:

SELECT DATEDIFF(day, 
                (SELECT MIN(ObservationDate) 
                 FROM TemperatureReadings),
                (SELECT MAX(ObservationDate) 
                 FROM TemperatureReadings)) AS TotalDaysRecorded;

Result:

TotalDaysRecorded
-----------------
265              

This returns 265 days between the earliest observation (January 10th at station WX-118) and the latest (October 2nd, also at WX-118). Each subquery runs independently, finds its respective date across all stations, and feeds that value into DATEDIFF().

Why Use Subqueries Instead of Separate Queries?

You might wonder why not just run two separate queries to get the MIN() and MAX() dates, then plug them into DATEDIFF() manually. While you could do that, the subquery approach offers several advantages. First, it’s self-contained – everything happens in one statement, which means you don’t need to manage multiple result sets or store intermediate values. Second, it automatically adapts to changes in your data. If new observations are added, the calculation updates without any code changes.

The subqueries also let you add WHERE clauses to filter your date range calculation. For example, if you wanted to know the span of observations only for a specific station, you could modify both subqueries:

SELECT DATEDIFF(day, 
                (SELECT MIN(ObservationDate) 
                 FROM TemperatureReadings 
                 WHERE StationCode = 'WX-042'),
                (SELECT MAX(ObservationDate) 
                 FROM TemperatureReadings 
                 WHERE StationCode = 'WX-042')) AS StationDaysRecorded;

Result:

StationDaysRecorded
-------------------
180                

Beyond MIN() and MAX()

While aggregate functions are the most common use case, you can use any subquery that returns a single date value. You might pull the date of a specific event, the most recent entry matching certain criteria, or a calculated date based on business logic. The main requirement is that each subquery must return exactly one date value – if it returns multiple rows or no rows, it won’t work, and you may get an error.

This technique works with any of DATEDIFF()‘s date parts – year, quarter, month, week, day, hour, minute, or second. Just swap out the first argument to match the granularity you need for your analysis.