如何比较 SQL Server 中的时间？

How can I compare time in SQL Server?

提问by AndreMiranda

I'm trying to compare time in a datetime fieldin 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.

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

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:00is less or greater than 07:30:00and I don't want to compare the date, just the timepart.

我问这个是因为我需要知道08:00:00是小于还是大于07:30:00并且我不想比较日期，只是时间部分。

Thanks!

谢谢！

回答by Andomar

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.

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

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 digit, 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:

1900-01-01 只是“零”日期；您可以使用 convert 显示时间部分，例如指定格式 108，这就是时间：

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.

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

回答by Iralda Mitro

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

Explanation:

解释：

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

如果你有varchar(5)你会得到HH:mm

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

如果你有varchar(8)你得到HH:mm ss

108 obtains only the time from the SQL date

108 只从SQL日期中获取时间

@leftTimeand @rightTimeare two variables to compare

@leftTime和@rightTime是两个要比较的变量

回答by Rob Garrison

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

如果您使用的是 SQL Server 2008，则可以执行以下操作：

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)

回答by Yi Zhang

Just change convert datetime to time that should do the trick:

只需将转换日期时间更改为应该可以解决问题的时间：

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

回答by Edgardo Correa

if (cast('2012-06-20 23:49:14.363' as time) between 
    cast('2012-06-20 23:49:14.363' as time) and 
    cast('2012-06-20 23:49:14.363' as time))

回答by Jason Musgrove

One (possibly small) issue I have noted with the solutions so far is that they all seem to require a function call to process the comparison. This means that the query engine will need to do a full table scan to seek the rows you are after - and be unable to use an index. If the table is not going to get particularly large, this probably won't have any adverse affects (and you can happily ignore this answer).

到目前为止，我在解决方案中注意到的一个（可能很小）问题是它们似乎都需要函数调用来处理比较。这意味着查询引擎将需要进行全表扫描以查找您要查找的行 - 并且无法使用索引。如果表不会变得特别大，这可能不会产生任何不利影响（您可以很乐意忽略此答案）。

If, on the other hand, the table could get quite large, the performance of the query could suffer.

另一方面，如果表可能变得非常大，则查询的性能可能会受到影响。

I know you stated that you do not wish to compare the date part - but is there an actual date being stored in the datetime column, or are you using it to store only the time? If the latter, you can use a simple comparison operator, and this will reduce both CPU usage, and allow the query engine to use statistics and indexes (if present) to optimise the query.

我知道您说过您不想比较日期部分 - 但是日期时间列中是否存储了实际日期，或者您是否仅使用它来存储时间？如果是后者，您可以使用简单的比较运算符，这将减少 CPU 使用率，并允许查询引擎使用统计信息和索引（如果存在）来优化查询。

If, however, the datetime column is being used to store both the date and time of the event, this obviously won't work. In this case if you can modify the app and the table structure, separate the date and time into two separate datetime columns, or create a indexed view that selects all the (relevant) columns of the source table, and a further column that contains the time element you wish to search for (use any of the previous answers to compute this) - and alter the app to query the view instead.

但是，如果 datetime 列用于存储事件的日期和时间，这显然不起作用。在这种情况下，如果您可以修改应用程序和表结构，将日期和时间分成两个单独的日期时间列，或者创建一个索引视图来选择源表的所有（相关）列，以及包含您希望搜索的时间元素（使用之前的任何答案来计算） - 并更改应用程序以查询视图。

回答by ronmurp

Using float does not work.

使用浮动不起作用。

DECLARE @t1 datetime, @t2 datetime
SELECT @t1 = '19000101 23:55:00', @t2 = '20001102 23:55:00'
SELECT CAST(@t1 as float) - floor(CAST(@t1 as float)), CAST(@t2 as float) - floor(CAST(@t2 as float))

You'll see that the values are not the same (SQL Server 2005). I wanted to use this method to check for times around midnight (the full method has more detail) in which I was comparing the current time for being between 23:55:00 and 00:05:00.

您会看到这些值不同 (SQL Server 2005)。我想用这个方法来检查午夜前后的时间（完整方法有更多细节），我比较了当前时间在 23:55:00 和 00:05:00 之间。

回答by Renato Bezerra

You can create a two variables of datetime, and set only hour of date that your need to compare.

您可以创建两个日期时间变量，并仅设置您需要比较的日期的小时数。

declare  @date1 datetime;
declare  @date2 datetime;

select   @date1 = CONVERT(varchar(20),CONVERT(datetime, '2011-02-11 08:00:00'), 114)
select   @date2 = CONVERT(varchar(20),GETDATE(), 114)

The date will be "1900-01-01" you can compare it

日期将是“1900-01-01”你可以比较它

if @date1 <= @date2
   print '@date1 less then @date2'
else
   print '@date1 more then @date2'

回答by Jhonny D. Cano -Leftware-

Adding to the other answers:

添加到其他答案：

you can create a function for trimming the date from a datetime

您可以创建一个用于从日期时间修剪日期的函数

CREATE FUNCTION dbo.f_trimdate (@dat datetime) RETURNS DATETIME AS BEGIN
    RETURN CONVERT(DATETIME, CONVERT(FLOAT, @dat) - CONVERT(INT, @dat))
END

So this:

所以这：

DECLARE @dat DATETIME
SELECT @dat = '20080201 02:25:46.000'
SELECT dbo.f_trimdate(@dat)

Will return 1900-01-01 02:25:46.000

将返回 1900-01-01 02:25:46.000

回答by littlechris

Use Datepart function: DATEPART(datepart, date)

使用Datepart函数： DATEPART(datepart, date)

E.g#

例如＃

SELECT DatePart(@YourVar, hh)*60) + DatePart(@YourVar, mi)*60)

SELECT DatePart(@YourVar, hh)*60) + DatePart(@YourVar, mi)*60)

This will give you total time of day in minutes allowing you to compare more easily.

这将以分钟为单位为您提供一天中的总时间，让您更轻松地进行比较。

You can use DateDiff if your dates are going to be the same, otherwise you'll need to strip out the date as above

如果您的日期相同，您可以使用 DateDiff，否则您需要删除上述日期