日期之间是否在SQL SERVER 2008中实际起作用 [英] Does BETWEEN with dates actually work in SQL SERVER 2008

问题描述

根据 cdonner ，在他的回答此处和他的博客。

According to cdonner, in his answer here and on his blog.

他声称与日期之间会产生不一致的结果

He claims that BETWEEN with date yields inconsistent results

来自他的博客：

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。

Notice that the S2 answer in the second query show "Out" when clearly the date should be in.

根据 cdonner ，之所以这样，是因为：

According to cdonner, the reason this is so is because:

SQL中DateTime类型的最低有效位是3毫秒

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

I think the cause is much simpler than that. I think it's because he's using strings and not dates in his query.

请原谅我的SQLServer-ish。我主要讲Oracle，所以这可能很难看。但是，当我接受他的查询（证明）时，出现了一个问题，并用日期时间变量替换了他的字符串，我得到了正确的输出。

Please excuse my SQLServer-ish. I speak mainly Oracle, so this may be ugly. But when I take his query that -proves- there's an issue and replace his strings with datetime variables I get the correct output.

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

哪个是正确的？

注意：解决争执或发动火焰战争。我真的想了解SQL Server BETWEEN是否比Oracle BETWEEN的功能少。我们在Oracle中没有这样的问题。

NB: this isn't an attempt to settle an argument or to start a flame war. I truly want to understand if SQL Server BETWEEN is less functional than the Oracle BETWEEN. WE have no such issue in Oracle.

推荐答案

在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

您正在此处比较字符串，而不是日期。

You are comparing strings here, not dates.

没有什么更好的 12/31/07和1 / 1/08 ，因为 2 以的 / 开头

There is nothing BETWEEN 12/31/07 and 1/1/08, as 2 goes after / in ASCII

这篇关于日期之间是否在SQL SERVER 2008中实际起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！