如何比较SQL Server中的时间?

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.

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

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.

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

Thanks!

谢谢!

16 个解决方案

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

长说明: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 -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之间的转换非常快，因为它们的存储方式基本相同。

#2

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

仅从SQL日期获得时间。

@leftTime and @rightTime are two variables to compare

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

#3

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)

#4

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))

#5

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.

我知道您已经声明过不希望比较日期部分——但是是否有一个实际的日期被存储在datetime列中，或者您只是使用它来存储时间?如果是后者，您可以使用一个简单的比较操作符，这将减少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列被用来存储事件的日期和时间，那么这显然是无效的。在这种情况下,如果你可以修改应用程序和表结构,不同的日期和时间为两个单独的datetime列,或创建一个索引视图,选择源表的所有列(相关),包含时间和进一步列元素你想搜索(使用任何以前的计算这个的答案),改变应用程序的查询视图。

#6

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之间的时间进行比较。

#7

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

只要将datetime转换为time就可以了:

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

#8

Adding to the other answers:

增加其他答案:

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

您可以创建一个函数，用于从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

#9

Use Datepart function: DATEPART(datepart, date)

使用Datepart函数:Datepart (Datepart, date)

E.g#

如#

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

选择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，否则您将需要删除上面的日期。

#10

I believe you want to use DATEPART('hour', datetime).

我相信您希望使用DATEPART('hour'， datetime)。

Reference is here:

参考这里:

http://msdn.microsoft.com/en-us/library/ms174420.aspx

http://msdn.microsoft.com/en-us/library/ms174420.aspx

#11

I don't love relying on storage internals (that datetime is a float with whole number = day and fractional = time), but I do the same thing as the answer Jhonny D. Cano. This is the way all of the db devs I know do it. Definitely do not convert to string. If you must avoid processing as float/int, then the best option is to pull out hour/minute/second/milliseconds with DatePart()

我不喜欢依赖于存储内部(datetime是一个浮点数= day，小数= time)，但我做的事情与答案Jhonny D. Cano相同。我认识的所有db开发人员都是这样做的。绝对不要转换成字符串。如果必须避免以浮点/int的形式进行处理，那么最好的选择是使用DatePart()提取小时/分钟/秒/毫秒

#12

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

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

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'

#13

SELECT timeEvent 
  FROM tbEvents 
 WHERE CONVERT(VARCHAR,startHour,108) >= '01:01:01'

This tells SQL Server to convert the current date/time into a varchar using style 108, which is "hh:mm:ss". You can also replace '01:01:01' which another convert if necessary.

这告诉SQL Server使用108样式将当前日期/时间转换为varchar，即“hh:mm:ss”。您还可以替换“01:01 . 01”，如果需要的话，可以进行另一个转换。

#14

I am assuming your startHour column and @startHour variable are both DATETIME; In that case, you should be converting to a string:

假设startHour列和@startHour变量都是DATETIME;在这种情况下，您应该转换为字符串:

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

#15

below query gives you time of the date

下面的查询给出了日期的时间。

select DateAdd(day,-DateDiff(day,0,YourDateTime),YourDateTime) As NewTime from Table

#16

@ronmurp raises a valid concern - the cast/floor approach returns different values for the same time. Along the lines of the answer by @littlechris and for a more general solution that solves for times that have a minute, seconds, milliseconds component, you could use this function to count the number of milliseconds from the start of the day.

@ronmurp提出了一个值得关注的问题——cast/floor方法同时返回不同的值。按照@littlechris给出的答案以及一个更通用的解决方案的思路，这个解决方案需要一分钟、一秒、一毫秒的时间，您可以使用这个函数从一天开始计算毫秒数。

Create Function [dbo].[MsFromStartOfDay] ( @DateTime datetime )
Returns int
As
  Begin
      Return (
               ( Datepart( ms , @DateTime ) ) +
               ( Datepart( ss , @DateTime ) * 1000 ) +
               ( Datepart( mi , @DateTime ) * 1000 * 60 ) +
               ( Datepart( hh , @DateTime ) * 1000 * 60 * 60 )
              )
  End

I've verified that it returns the same int for two different dates with the same time

我已经验证过，对于两个不同的日期，它同时返回相同的int数

declare @first datetime
set @first = '1900-01-01 23:59:39.090'
declare @second datetime
set @second = '2000-11-02 23:56:39.090'
Select dbo.MsFromStartOfDay( @first )
Select dbo.MsFromStartOfDay( @second )

This solution doesn't always return the int you would expect. For example, try the below in SQL 2005, it returns an int ending in '557' instead of '556'.

这个解决方案并不总是返回预期的int值。例如，在SQL 2005中尝试下面的操作，它返回一个以'557'而不是'556'结尾的int。

set @first = '1900-01-01 23:59:39.556'
set @second = '2000-11-02 23:56:39.556'

I think this has to do with the nature of DateTime stored as float. You can still compare the two number, though. And when I used this approach on a "real" dataset of DateTime captured in .NET using DateTime.Now() and stored in SQL, I found that the calculations were accurate.

我认为这与以浮点形式存储的DateTime的性质有关。你仍然可以比较这两个数字。当我使用这种方法在. net中使用DateTime. now()和SQL存储的“真实”数据集时，我发现计算是准确的。