仅使用日期而非时间的 SQL 日期比较

SQL Date Compare by only using Date not Time

提问by Gage

This was originally going to be a question about how to implement this because I was stuck at a certain part but I am now curious as to why this was happening in the first place. I needed to compare only the dates not the time which wouldn't have been a problem if the times didn't differ. The code below shows the query I was originally trying

这最初是关于如何实现这一点的问题，因为我被困在某个部分，但我现在很好奇为什么会发生这种情况。我只需要比较日期而不是时间，如果时间没有不同，这不会成为问题。下面的代码显示了我最初尝试的查询

SELECT *
FROM Employee e
inner join OT_Hours o on o.Emp_ID=e.Emp_ID
inner join Position p on p.Position_ID=e.Position_ID
inner join Signup_Sheet s on s.Employee_ID=e.Emp_ID
WHERE e.Eligible_OT=1 and s.Day_Shift = 1 
and p.Position_Name = 'Controller' 
and Convert(Varchar(20),s.Date,101) = '07/26/2010'
and Convert(Varchar(20),o.Date,101) <='07/26/2010'
and Convert(Varchar(20),o.Date,101) > '07/26/2009'
and o.Quantity NOT IN(0.3) order by o.Date DESC

I would get no result when I ran that query, but when I removed the second last line it would return 12 results (<=) and when I removed the 3rd last line but kept the second last it would return 6 results (>). After reviewing the data I could see that 4 of those results should have been returned. Now for the wierd part. Below is the code I am currently using.

当我运行该查询时，我不会得到任何结果，但是当我删除倒数第二行时，它将返回 12 个结果（<=），当我删除倒数第三行但保留倒数第二行时，它将返回 6 个结果（>）。查看数据后，我可以看到应该返回其中的 4 个结果。现在是奇怪的部分。下面是我目前使用的代码。

SELECT DISTINCT o.Date, e.Emp_ID as Emp_ID, e.First_Name+ ' ' +e.Last_Name as Name, o.Quantity as Sum
FROM Employee e
left join OT_Hours o on o.Emp_ID=e.Emp_ID
left join Position p on p.Position_ID=e.Position_ID
left join Signup_Sheet s on s.Employee_ID=e.Emp_ID
WHERE e.Eligible_OT=1 and s.Day_Shift = 1 
and p.Position_Name = 'Controller' 
and Convert(Varchar(20),s.Date,101) = '07/26/2010'
and o.Date between '07/26/2009' and '07/26/2010'
and o.Quantity NOT IN(0.3) order by o.Date DESC

This query will return results but I also tested it like I did the other one when o.Date was above and below the date specified. When the date was <= 16 results were returned, when > 8 results were returned. The final query produced 6 results. Now this is not a production database I'm querying against and I'm the only one using it so the data did not change. Any explanation on why this was happening? I'm assuming it had something to do with converting it to varchar and it couldn't compare properly but that doesn't explain why I would get 12 <=, 6 > and then no results in the end. Also if anyone knows a better way to implement this please let me know.

此查询将返回结果，但我也对其进行了测试，就像我在 o.Date 高于和低于指定日期时所做的另一个查询一样。当日期 <= 16 个结果被返回，当 > 8 个结果被返回。最终查询产生了 6 个结果。现在这不是我要查询的生产数据库，我是唯一一个使用它的人，因此数据没有改变。关于为什么会发生这种情况的任何解释？我假设它与将它转换为 varchar 有关系，它无法正确比较，但这并不能解释为什么我会得到 12 <=, 6 > 然后最后没有结果。另外，如果有人知道实现这一点的更好方法，请告诉我。

采纳答案by Chris Diver

In the third from last line in your first query you are comparing two strings.

在第一个查询的倒数第三行中，您正在比较两个字符串。

As such 01/02/2009is greater than 01/01/2010

因此01/02/2009大于01/01/2010

I usually do date BETWEEN '01/02/2009 00:00:00.000' AND '01/01/2010 23:59:59.997'but it will be interesting to see a better solution.

我通常会约会，BETWEEN '01/02/2009 00:00:00.000' AND '01/01/2010 23:59:59.997'但看到更好的解决方案会很有趣。

回答by OMG Ponies

The two queries aren't the same - this:

这两个查询不一样 - 这是：

and o.Date between '07/26/2009' and '07/26/2010'

...is the equivalent of:

...相当于：

and o.Date >= '07/26/2009' 
and o.Date <= '07/26/2010'

BETWEEN is ANSI standard, and inclusive on every database I've ever encountered.

BETWEEN 是 ANSI 标准，包括我遇到的每个数据库。

Mind that if you don't specify a time portion for DATETIMEs, the value defaults to starting at midnight of the day - 00:00:00.

请注意，如果您没有为 DATETIME 指定时间部分，则该值默认为从当天午夜 - 00:00:00 开始。

回答by Ryan Ische

I learned this technique from SQL Server Magazine February 2007 (Datetime Calculations by Itzik Ben-Gan). This way, your 'between' will work regardless of whether the row's date is after midnight, as everything has been normalized to be at midnight with this comparison:

我从 2007 年 2 月的 SQL Server 杂志（Itzik Ben-Gan 的 Datetime Calculations）学到了这项技术。这样，无论行的日期是否在午夜之后，您的“介于”都将起作用，因为通过此比较，一切都已标准化为午夜：

select *
from someTable
where dateadd(day, datediff(day, 0, somedate), 0) between '07/26/2009' and '07/26/2010' 

The datediff and dateadd work together to strip off the time and leave the date. You can then compare it to string literals, or other dates that have had the same modification done. I'd recommend putting this in a function.

datediff 和 dateadd 一起工作以去除时间并保留日期。然后，您可以将其与字符串文字或其他已完成相同修改的日期进行比较。我建议把它放在一个函数中。

EDIT: Based on OMG Ponies' comments. This will not take advantage of an index on the date column. An alternative might be to use the time stripping technique in addition to the technique others have mentioned. So instead of doing it on the table's column, do it on the last argument to 'between'. You could have a function like so:

编辑：基于 OMG 小马的评论。这不会利用日期列上的索引。除了其他人提到的技术之外，另一种可能是使用时间剥离技术。因此，不要在表的列上执行此操作，而是在 'between' 的最后一个参数上执行此操作。你可以有这样的功能：

CREATE FUNCTION [dbo].[fn_enddate](@enddate datetime)
RETURNS datetime AS  
BEGIN
    DECLARE @endOfDay datetime
    set @endOfDay = dateadd(millisecond, -2, dateadd(day, datediff(day, 0, @enddate) + 1, 0))
    return @endOfDay
END

This takes the argument date, sets it to midnight the next day, then subtracts two milliseconds, giving the end of the day of the given datetime. So then you could do:

这需要参数日期，将其设置为第二天的午夜，然后减去两毫秒，给出给定日期时间的一天结束。那么你可以这样做：

select *
from someTable
where somedate between '07/26/2009' and dbo.fn_enddate('07/26/2010')

回答by Tom H

My suggestion would be to adjust your criteria to cover times, not the dates themselves. If you convert or otherwise manipulate the column(s) for the compare you can destroy the use of indexes in the query.

我的建议是调整您的标准以涵盖时间，而不是日期本身。如果您转换或以其他方式操作用于比较的列，您可能会破坏查询中索引的使用。

Also, always compare dates to dates. When you convert them to strings and try to compare you run into problems as Chris Diver points out.

此外，始终将日期与日期进行比较。当您将它们转换为字符串并尝试进行比较时，您会遇到 Chris Diver 指出的问题。

In your case, I would try:

在你的情况下，我会尝试：

SELECT
    o.Date,
    e.Emp_ID as Emp_ID,
    e.First_Name+ ' ' +e.Last_Name as Name,
    o.Quantity as Sum
FROM
    Employee e
LEFT JOIN OT_Hours o ON o.Emp_ID = e.Emp_ID
LEFT JOIN Position p ON p.Position_ID = e.Position_ID
LEFT JOIN Signup_Sheet s ON s.Employee_ID = e.Emp_ID
WHERE
    e.Eligible_OT = 1 AND
    s.Day_Shift = 1 AND
    p.Position_Name = 'Controller' AND
    (s.Date >= @signup_date AND s.Date < DATEADD(dy, 1, @signup_date)) AND
    (o.Date >= @order_start_date AND o.Date < DATEADD(dy, 1, @order_end_date)) AND
    o.Quantity NOT IN (0.3)
ORDER BY
    o.Date DESC

You'll need to make sure that the parameters or variables are always without a time portion.

您需要确保参数或变量始终没有时间部分。

回答by Wil P

If your database is SQL Server what I have done that workS pretty well to strip off the time is something like the following....

如果您的数据库是 SQL Server，我所做的工作非常好，可以减少时间，如下所示......

SELECT DISTINCT o.Date, e.Emp_ID as Emp_ID, e.First_Name+ ' ' +e.Last_Name as Name, o.Quantity as Sum 
FROM Employee e 
left join OT_Hours o on o.Emp_ID=e.Emp_ID 
left join Position p on p.Position_ID=e.Position_ID 
left join Signup_Sheet s on s.Employee_ID=e.Emp_ID 
WHERE e.Eligible_OT=1 and s.Day_Shift = 1  
and p.Position_Name = 'Controller'  
and CAST(FLOOR(CAST(s.Date AS FLOAT)) AS DATETIME) = '07/26/2010' 
and CAST(FLOOR(CAST(o.Date AS FLOAT)) AS DATETIME) between '07/26/2009' and '07/26/2010' 
and o.Quantity NOT IN(0.3) order by o.Date DESC 

Depending on how your parameters are set '07/26/2010', '07/26/2009' you could store them in datetime variables and perform the same cast(floor(cast(@datevar as float)) as datetime)operation.

根据您的参数设置方式“07/26/2010”、“07/26/2009”，您可以将它们存储在日期时间变量中并执行相同的cast(floor(cast(@datevar as float)) as datetime)操作。

This seems to be a repost. Check the accepted answer here... How to remove the time portion of a datetime value (SQL Server)?

这似乎是一个转贴。检查这里接受的答案...如何删除日期时间值的时间部分（SQL Server）？

回答by LukeH

...
AND s.Date BETWEEN '2010-07-26 00:00:00.000' AND '2010-07-26 23:59:59.997'
AND o.Date BETWEEN '2009-07-26 00:00:00.000' AND '2010-07-26 23:59:59.997'
...