如何比较SQL Server中的时间？ [英] How can I compare time in SQL Server?

问题描述

我试图在SQL查询中的 datetime字段中比较时间，但我不知道它是对的。我不想比较日期部分，只是时间部分。

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

我这样做：

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

是正确的吗？

我问这个是因为我需要知道'08：00：00'是否小于或大于'07：30：00'，我不想要比较日期，只是时间部分。

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.

谢谢！

推荐答案

您的比较将会起作用，但是它会很慢，因为日期会转换为每行的字符串。要有效地比较两个时间部分，请尝试：

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

长说明：SQL Server中的日期存储为浮点数。小数点前的数字表示日期。小数点后的数字表示时间。

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'

我们把它转换成一个float：

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 digit, i.e. the time:

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

转换它返回到datetime：

Convert it back to a datetime:

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

1900-01-01只是零的日期;您可以使用转换显示时间部分，例如格式为108，这只是时间：

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

转换在datetime和float之间是相当快的，因为它们基本上以相同的方式存储。

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

这篇关于如何比较SQL Server中的时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！