How to Efficiently Compare Time Values in SQL Server?

Comparing Time in SQL Server

When comparing time values in a SQL query, it's crucial to isolate the time component from the date. The given approach of using convert(datetime, ...) to string representations and then comparing is not recommended due to its slow performance.

A more efficient method is to leverage the intrinsic floating-point representation of SQL Server dates. The integer portion represents the date, while the decimal portion represents the time.

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

This approach subtracts the dates' integral parts, leaving only the decimal (time) portions. The resulting Difference represents the time difference.

For example, comparing '08:00:00' and '07:30:00' in SQL Server using this method:

declare @first datetime
set @first = '08:00:00.000'
declare @second datetime
set @second = '07:30:00.000'

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

Result:

Difference
------------
0.5

This confirms that '08:00:00' is greater than '07:30:00' by 30 minutes.

The above is the detailed content of How to Efficiently Compare Time Values in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!