How can I compare time in SQL Server?

Question

I'm trying to compare time in a datetime field in a SQL query, but I don't know if it's right. I don't want to compare the date part, just the time part.

I'm doing this:

SELECT timeEvent  FROM tbEvents  WHERE convert(datetime, startHour, 8) >= convert(datetime, @startHour, 8) 

Is it correct?

I'm asking this because I need to know if 08:00:00 is less or greater than 07:30:00 and I don't want to compare the date, just the time part.

Thanks!

Answer 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.