#0329 – SQL Server – using DATEDIFF to compare two time values

Today’s post is a simple one, but inspired from a question I encountered in one of the forums. Comparing dates and date-time values are fairly common requirements and we already know a solution for realization of this requirement – the DATEDIFF function.

However, the query that was posted on the forums was about comparison of time values. Let us assume the requirement to be such that if the current time is less than 08:00AM, the default time being returned should be 08:00AM. Else, it can be whatever the current time is. The person who posted the query apparently wanted to know how to achieve this requirement.

The DATEDIFF function can help us here as well. DATEDIFF can work with date, time and date-time values.Here’s an example:

USE tempdb;
GO
DECLARE @referenceValue TIME = '08:00';
DECLARE @comparisonValue TIME = '07:30';

--Let us check the output for a value that is less
--than the reference value
SELECT @comparisonValue AS ComparisonValue,
       @referenceValue AS ReferenceValue,
       CASE WHEN DATEDIFF(SECOND,@referenceValue,@comparisonValue) &lt; 0
            THEN '08:00'
            ELSE @comparisonValue
            END AS ReturnTime;

--Now, let us check the output for a value that is greater
--than the reference value
SELECT @comparisonValue = '08:30';

SELECT @comparisonValue AS ComparisonValue,
       @referenceValue AS ReferenceValue,
       CASE WHEN DATEDIFF(SECOND,@referenceValue,@comparisonValue) &lt; 0
            THEN '08:00'
            ELSE @comparisonValue
            END AS ReturnTime;
GO

Here’s the output: