SQL SERVER 2008中的日期BETWEEN查询是否有效？

Question

根据cdonner在他的回答这里和他的博客中所述，他声称使用日期的BETWEEN运算符会产生不一致的结果。 从他的博客中得知：

select
    case when '9/1/08' between '9/1/08' and '9/15/08'
        then 'in' else 'out' end as s1,
    case when '9/1/08' between '8/28/08' and '9/1/08'
        then 'in' else 'out' end as s2

s1   s2
---- ----
in   in

(1 row(s) affected)
select
    case when '1/1/08' between '1/1/08' and '2/1/08'
        then 'in' else 'out' end as s1,
    case when '1/1/08' between '12/31/07' and '1/1/08'
        then 'in' else 'out' end as s2

s1   s2
---- ----
in   out

(1 row(s) affected

注意，第二个查询中的S2答案显示为“Out”，而日期显然应该是在内部。

根据cdonner的说法，原因是：

SQL中DateTime类型的最低有效数字为3毫秒

我认为原因比那简单得多。我认为这是因为他在查询中使用字符串而不是日期。

请原谅我的SQLServer-ish。我主要讲Oracle，所以可能很丑陋。但是，当我将他证明有问题的查询替换为datetime变量时，我得到了正确的输出。

DECLARE @Jan108 datetime
DECLARE @Feb108 datetime
DECLARE @Dec3107 datetime

SET @Jan108 = '1/1/08'
SET @Feb108 = '2/1/08'
SET @Dec3107 = '12/31/07'

select
    case when @Jan108 between @Jan108 and @Feb108
        then 'in' else 'out' end as s1,
    case when @Jan108 between @Dec3107 and @Jan108
        then 'in' else 'out' end as s2

哪个是正确的？

NB：这不是为了解决争论或开始一场争吵。我真的想知道SQL Server BETWEEN是否比Oracle BETWEEN功能更少。我们在Oracle中没有这样的问题。

- Mark Brady

---

如果你已经知道博主因将字符串比较与日期比较混淆而出了错，那么你在问什么呢？ - Jeffrey L Whitledge

我不知道那个。这就是我为什么在问的原因。他驳斥了我的说法，认为这与3ms最低有效位有关，并声称它与字符串无关。我从未听说过这个，所以正在寻求证实。我是一个Oracle的人，想知道MSSS是否真的会这样表现。 - Mark Brady

Answer 1

在Oracle中：

select
    case when '1/1/08' between '1/1/08' and '2/1/08'
        then 'in' else 'out' end as s1,
    case when '1/1/08' between '12/31/07' and '1/1/08'
        then 'in' else 'out' end as s2
FROM dual

in out

这里比较的是字符串，而不是日期。

在 12/31/07 和 1/1/08 之间没有任何东西，因为在 ASCII 中，/ 后面是 2。

- Quassnoi

---

我并没有比较字符串……是cdonner在比较。但是没错，那正是我的观点。 - Mark Brady

Answer 2

我必须使用类似这样的东西：

Declare @BeginDate SmallDateTime
Declare @EndDate SmallDateTime
Set @BeginDate = '2007-08-01'
Set @EndDate = '2007-08-31'

Select *
From dbo.table1 a
Where a.session_date Between @BeginDate + ' 00:00:00' And @EndDate + ' 23:59:59'
Order By a.session_date asc

获取正确的日期时间区间

- GregD

Answer 3

SQL Server将日期时间值存储为数字。例如，0表示1900年01月01日 00:00:00.000。

你在问题中提供的示例存在舍入问题，类似于浮点数1.0被存储为0.99999...的方式。

为了准确比较日期，您应该将值转换为datetime类型，然后进行比较。

SELECT
CASE 
    WHEN cast('1/1/08' as datetime) 
        BETWEEN cast('1/1/08' as datetime) AND cast('2/1/08' as datetime) 
    THEN 'in' ELSE 'out' 
END AS s1,
CASE 
    WHEN cast('1/1/08' as datetime) 
        BETWEEN cast('12/31/07' as datetime) AND cast('1/1/08' as datetime) 
    THEN 'in' ELSE 'out' 
END AS s2

这将导致您期望的输出：s1==in，s2==in。

- Angry Jim

---

哪个示例受到舍入的影响？当Quassnoi说“在ASCII中，12/31/07和1/1/08之间没有任何东西，因为2在/之后”时，我非常确定他是正确的...... 舍入问题出在哪里？ - Mark Brady

Answer 4

你是正确的，他的代码由于字符串比较存在缺陷。

然而，如果你使用datetime类型而不是新的date类型，这并不重要。原因是通常情况下你不想进行包含搜索，所以不需要编写如下代码：

SELECT * FROM [MyTable] WHERE MyDateColumn BETWEEN @StartDate AND @EndDate

通常你会这样写：

SELECT * FROM [MyTable] WHERE MyDateColumn >= @StartDate AND MyDateColumn < @EndDate

其中@EndDate实际上比你真正想要的那一天多一个。

我预计这个问题已经在新的日期类型中得到解决，但是我手头没有SQL Server 2008，所以无法测试。

- Joel Coehoorn

---

如果你像我一样重写查询，就不必选择比你实际想要的日期晚一天。 - GregD

该代码将首先将值转换为nvarchar，然后再将其转换回datetime，对于两个日期都是如此，并且不考虑完整datetime值的毫秒。另一种常见策略是只需说“AND MyDateCol < @EndDate + 1”。 - Joel Coehoorn

也许在您的领域里很典型，但在我的领域里，我们几乎总是想要包含搜索。因此，回到问题本身，BETWEEN 在 SQL SERVER 中是否有效，或者问题仅仅是他正在使用字符串这一事实？ - Mark Brady

它在SQL Server中确实有效- 我的回答的第一行是他的代码有缺陷。 - Joel Coehoorn

Answer 5

非常有用的讨论！

我遇到了类似的问题，通过使用CAST和CONVERT解决了问题，如下所示：

cast(CONVERT(varchar(8), [PostDate], 112)AS DATE) BETWEEN '2013-01-01' AND '2013-01-31'

这个方法非常好用，经过多次详细验证证明。

“Cast（”是必需的（相信我，我有秃顶证明）。但不需要声明变量等。

如果我的方法或回答有误，希望得到反馈。

谢谢！

- Dan Alvear

Answer 6

博客上的代码是无意义的，我要感谢马克指出了这一点。问题确实存在。它与BETWEEN无关，而与四舍五入有关（在某些情况下可能导致BETWEEN失败）。虽然SQL Server会进行四舍五入，但.NET不会，在非常短的时间内进行多次插入时，我遇到了问题，我认为我具有不同的日期时间值，但由于四舍五入，它们在数据库中变成了相同的值。这篇文章早就被纠正了，代码示例仍然适用于SQL Server 2008。

如果您运行此查询，您将看到我所指的内容：

select convert(varchar(32), convert(datetime,
                            '9/1/08 00:00:00.005'), 121);

- cdonner

Answer 7

永远不要在日期时间值上使用BETWEEN。在MySQL中，我可以这样做

created >= CURDATE() - INTERVAL 1 DAY AND created < CURDATE()

将创建时间限制在昨天 (整天) 内。

使用 BETWEEN '2011-05-02' AND '2011-05-02 23:59:59'，我可能会赌错一秒钟，错过了创建时间为 '2011-05-02 23:59:59.001' 的记录。

- druid

Answer 8

他完全错了 - 它正在比较字符串

如果你将它们转换为日期时间或用日期变量替换它们，它就可以正常工作：

select
    case when CAST('JAN 01 2008' as smalldatetime)
        between CAST('JAN 01 2008' as smalldatetime)
        and CAST('FEB 01 2008' as smalldatetime)
        then 'in' else 'out' end as s1,
    case when CAST('JAN 01 2008' as smalldatetime)
        between CAST('DEC 31 2007' as smalldatetime)
        and CAST('JAN 01 2008' as smalldatetime)
        then 'in' else 'out' end as s2

- DJ

---

好的，这正是我想的。我真诚地想知道是否有什么我不知道的东西，因为有足够的手挥和最低有效数字的东西让我感到疑惑。 - Mark Brady