Sometimes you need to measure how many days have passed between a specific reference point and a constantly moving target. SQL Server’s DATEDIFF() function handles this elegantly by letting you combine hardcoded dates with dynamic functions like GETDATE(). This can be useful for calculating things like age, days since an event, or time remaining until a deadline.
The main point here is that DATEDIFF() doesn’t care whether its date arguments are literals, functions, or even subqueries. It just needs two date values to compare. When you use GETDATE() or similar functions, you’re telling SQL Server to calculate the difference based on the current moment, which means the result changes every time you run the query.
Example
Let’s say you’re managing a fleet of lab equipment, and you need to calculate when each piece is due for its next calibration. All equipment must be recalibrated every 90 days from its last calibration date. You want to know how many days until each item’s next calibration, calculated as of tomorrow (to plan your schedule for the next day).
Here’s a simple example of what your table and data might look like:
-- Create the equipment table
CREATE TABLE LabEquipment (
EquipmentID INT PRIMARY KEY IDENTITY(1,1),
DeviceName VARCHAR(100),
SerialNumber VARCHAR(50),
LastCalibrationDate DATE,
TechnicianName VARCHAR(50)
);
-- Insert sample data with various calibration dates
INSERT INTO LabEquipment (DeviceName, SerialNumber, LastCalibrationDate, TechnicianName)
VALUES
('Spectrophotometer', 'SP-4429', '2025-08-15', 'Jordan Chen'),
('pH Meter', 'PH-8821', '2025-09-22', 'Sam Rivera'),
('Centrifuge', 'CF-3304', '2025-10-08', 'Alex Kim'),
('Microscope', 'MC-7756', '2025-11-01', 'Morgan Taylor'),
('Autoclave', 'AC-2198', '2025-07-30', 'Casey Brooks');Now here’s a query to get the required information:
SELECT DeviceName,
LastCalibrationDate,
DATEADD(day, 90, LastCalibrationDate) AS NextCalibrationDue,
DATEDIFF(day, DATEADD(day, 90, LastCalibrationDate), GETDATE() + 1) AS DaysOverdue
FROM LabEquipment;Result:
DeviceName LastCalibrationDate NextCalibrationDue DaysOverdue
----------------- ------------------- ------------------ -----------
Spectrophotometer 2025-08-15 2025-11-13 9
pH Meter 2025-09-22 2025-12-21 -29
Centrifuge 2025-10-08 2026-01-06 -45
Microscope 2025-11-01 2026-01-30 -69
Autoclave 2025-07-30 2025-10-28 25
This query calculates how many days each piece of equipment is overdue (or how many days until it’s due) as of tomorrow. I ran this on November 21st, 2025, and so we can see for example, the Spectrophotometer that was last calibrated on August 15th is already overdue (its next calibration was due November 13th). On the other hand, the Microscope calibrated on November 1st isn’t due until January 30th, 2026.
Understanding the Calculation
First, DATEADD(day, 90, LastCalibrationDate) calculates when the next calibration is due by adding 90 days to each piece of equipment’s last calibration date. Then DATEDIFF() compares that due date against GETDATE() + 1 (tomorrow’s date).
When the result is positive, the equipment is overdue by that many days. When it’s negative, the equipment isn’t due yet and the absolute value tells you how many days remain. This calculation is different for each row because it depends on the LastCalibrationDate column, which varies by equipment.
A Simpler Variation
If you don’t need the intermediate calculation, you can simplify it to show days since the last calibration as of tomorrow:
SELECT DeviceName,
LastCalibrationDate,
DATEDIFF(day, LastCalibrationDate, GETDATE() + 1) AS DaysSinceLastCalibration
FROM LabEquipment;Result:
DeviceName LastCalibrationDate DaysSinceLastCalibration
----------------- ------------------- ------------------------
Spectrophotometer 2025-08-15 99
pH Meter 2025-09-22 61
Centrifuge 2025-10-08 45
Microscope 2025-11-01 21
Autoclave 2025-07-30 115
This tells you how many days it will have been since each calibration as of tomorrow. You can then mentally compare that to your 90-day threshold, or add a CASE expression to flag overdue items.
Why Add the Extra Day?
Adding 1 to GETDATE() gives you tomorrow’s perspective, which can be useful when you’re generating a work schedule or planning report at the end of the current day. If you run this query at 11 PM tonight, you’re seeing what the status will be tomorrow morning when your team starts work. For real-time “right now” calculations, you’d just use GETDATE() without the addition.
You can use any date part with this pattern (hour, minute, month, year, etc) depending on the granularity you need. Just remember that GETDATE() returns the current server time, so if you’re working across time zones, you might want to use GETUTCDATE() instead for consistency.