How can I compare time in SQL Server?

Solution 1:

Your compare will work, but it will be slow because the dates are converted to a string for each row. To efficiently compare two time parts, try:

declare @first datetime
set @first = '2009-04-30 19:47:16.123'
declare @second datetime
set @second = '2009-04-10 19:47:16.123'

select (cast(@first as float) - floor(cast(@first as float))) -
       (cast(@second as float) - floor(cast(@second as float)))
       as Difference

Long explanation: a date in SQL server is stored as a floating point number. The digits before the decimal point represent the date. The digits after the decimal point represent the time.

So here's an example date:

declare @mydate datetime
set @mydate = '2009-04-30 19:47:16.123'

Let's convert it to a float:

declare @myfloat float
set @myfloat = cast(@mydate as float)
select @myfloat
-- Shows 39931,8244921682

Now take the part after the comma character, i.e. the time:

set @myfloat = @myfloat - floor(@myfloat) 
select @myfloat
-- Shows 0,824492168212601

Convert it back to a datetime:

declare @mytime datetime
set @mytime = convert(datetime,@myfloat)
select @mytime
-- Shows 1900-01-01 19:47:16.123

The 1900-01-01 is just the "zero" date; you can display the time part with convert, specifying for example format 108, which is just the time:

select convert(varchar(32),@mytime,108)
-- Shows 19:47:16

Conversions between datetime and float are pretty fast, because they're basically stored in the same way.

Solution 2:

convert(varchar(5), thedate, 108) between @leftTime and @rightTime

Explanation:

if you have varchar(5) you will obtain HH:mm

if you have varchar(8) you obtain HH:mm ss

108 obtains only the time from the SQL date

@leftTime and @rightTime are two variables to compare

Solution 3:

If you're using SQL Server 2008, you can do this:

WHERE CONVERT(time(0), startHour) >= CONVERT(time(0), @startTime)

Here's a full test:

DECLARE @tbEvents TABLE (
    timeEvent   int      IDENTITY,
    startHour   datetime
)

INSERT INTO @tbEvents (startHour) SELECT DATEADD(hh, 0, GETDATE())
INSERT INTO @tbEvents (startHour) SELECT DATEADD(hh, 1, GETDATE())
INSERT INTO @tbEvents (startHour) SELECT DATEADD(hh, 2, GETDATE())
INSERT INTO @tbEvents (startHour) SELECT DATEADD(hh, 3, GETDATE())
INSERT INTO @tbEvents (startHour) SELECT DATEADD(hh, 4, GETDATE())
INSERT INTO @tbEvents (startHour) SELECT DATEADD(hh, 5, GETDATE())

--SELECT * FROM @tbEvents

DECLARE @startTime  datetime

SET @startTime = DATEADD(mi, 65, GETDATE())

SELECT
    timeEvent,
    CONVERT(time(0), startHour)  AS 'startHour',
    CONVERT(time(0), @startTime) AS '@startTime'
FROM @tbEvents
WHERE CONVERT(time(0), startHour) >= CONVERT(time(0), @startTime)