#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) < 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) < 0
            THEN '08:00'
            ELSE @comparisonValue
            END AS ReturnTime;
GO

Here’s the output:

image

Further Reading

  • Msg 402 – The data types datetime and time are incompatible in the add/subtract operator [Link]
  • DATEDIFF date comparison function [Books On Line Link]

Until we meet next time,
Be courteous. Drive responsibly.

 

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s