SQL Date通过仅使用日期比较而不是时间

问题描述：

这本来是一个关于如何实现这个功能的问题，因为我被困在某个部分，但我现在好奇为什么这首先发生。我只需要比较日期，而不是时间不会有问题的时间，如果时间没有不同。下面的代码显示我原本是想SQL Date通过仅使用日期比较而不是时间

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

我会得到任何结果，当我跑了查询的查询，但是当我删除了倒数第二行，它将返回12个结果（< =），当我删除了最后第3行，但保持倒数第二，它将返回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

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

这已被问（在某种程度上）检查接受的答案在这里... http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion -of-datetime-value-sql-server – 2010-07-27 16:31:31

答

在从最后一行第三在你第一次查询你比较两个字符串。

这样01/02/2009大于01/01/2010

我通常做的日期BETWEEN '01/02/2009 00:00:00.000' AND '01/01/2010 23:59:59.997'但是这将是有趣的，看看一个更好的解决方案。

SQL Server执行隐式数据类型转换，从字符串到datetime提供格式匹配。 – 2010-07-27 16:08:55

'SELECT CASE WHEN '01/02/2009'> '01/01/2010'THEN 1 ELSE 0 END'在我的SQL框中返回1。 'o.Date> '07/26/2009''将被隐含地转换，但不是按照第一个查询的两个varchar。 – 2010-07-27 16:10:20

没有其他选择，但你回答了我的问题，为什么它没有正确比较日期。没想到2009年1月2日是大于01/01/2010笑 – Gage 2010-07-27 17:27:03

答

如果你的数据库是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

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

这似乎是一个转发。请在这里接受的答案... How to remove the time portion of a datetime value (SQL Server)?

答

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

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

...是等价的：

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

之间ANSI标准，并且包含在我遇到的每个数据库中。

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

答

我从SQL Server Magazine 2007年2月（Itzik Ben-Gan的日期时间计算）学习了这种技术。

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

的DATEDIFF和DATEADD共同努力，剥离时间：这样一来，你的“之间”将不管该行的日期是否在午夜之后，因为一切都已经常态化是在午夜，这个比较工作并离开日期。然后，您可以将其与字符串文字或已完成相同修改的其他日期进行比较。我建议把它放在一个函数中。编辑：基于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

这需要论证迄今为止，第二天将到午夜，然后减去两毫秒，给定日期时间的一天结束。所以，那么你可以做：

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

我以前没见过这个。很高兴知道。他们是否谈过CAST（FLOOR（CAST（''）方法？ – 2010-07-27 16:18:59

我不记得了，他比较了几个，这个似乎是最好的。 – 2010-07-27 16:20:14

我不推荐使用这个 - 使用功能在列上呈现索引，如果就位，无用 – 2010-07-27 16:25:54

答

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

答

我的建议是调整你的标准来覆盖时间，而不是日期本身。如果您转换或以其他方式处理比较的列，则可以销毁查询中索引的使用。

此外，总是比较日期和日期。当你将它们转换为字符串并尝试比较时，你会像Chris Diver指出的那样遇到问题。

在你的情况，我会尝试：

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

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

SQL Date通过仅使用日期比较而不是时间

相关推荐