Comparing time part only without comparing date part in SQL


Recently one of my colleagues asked how to compare only the time part of the DateTime field. I have seen many of us struggling to find an answer for this (Winking smile including me).

If you are one of them, here is a simple solution…

DECLARE @date_to_compare AS DATETIME = '2010-08-08 05:00:00';
DECLARE @date_start AS DATETIME = '2012-08-21 7:00:00';
DECLARE @date_end AS DATETIME = '2012-10-07 13:00:00';

SELECT 'OK'
WHERE  DATEADD(day, -DATEDIFF(day, 0, @date_to_compare), @date_to_compare)
BETWEEN DATEADD(day, -DATEDIFF(day, 0, @date_start), @date_start) AND DATEADD(day, -DATEDIFF(day, 0, @date_end), @date_end)

Above solution work as follows,
It converts the date part of the entered date in to the SQL default date, and then compares it.

SELECT DATEADD(day, -DATEDIFF(day, 0, @date_to_compare), @date_to_compare)
>> 1900-01-01 05:00:00.00
Advertisements

Leave a Reply

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