sql - Oracle 根据返回不一致结果的年份选择日期

Question

我在修改 Oracle View 时遇到一个特殊问题。我的想法是我有一个存储所有选举候选人的基表，还有一个选举日期列，告诉我们这个候选人参加了哪次选举，就像这样。

NAME       | ELECTION_DATE
---------------------------
John Smith | 01-APR-2011
Alan Cooper| 02-MAY-2013

ELECTION_DATE 列存储为日期。我们为 2011 年和 2013 年构建了 2 个 View ，使用以下 2011 年的声明。

where election_date >= to_date(2011,'yyyy') and election_date < to_date(2012,'yyyy')

我们认为它只会拾取上面的 John Smith 记录，但它没有。奇怪的是，当我们为 2013 年的 View 执行此操作时。

where election_date >= to_date(2013,'YYYY') and election_date < to_date(2014,'YYYY')

它确实获得了 Alan Cooper 的记录。更奇怪的是，如果我们为 2011 年的 View 执行此操作。

where election_date >= to_date(2010,'yyyy') and election_date < to_date(2011,'yyyy')

我们只得到了 John Smith 的记录，而不是 Alan Cooper 的记录，这是我们想要的，但不是我们所期望的。

然后我们将它们都更改为 between statements，它们的行为完全相同，即 2010 年至 2011 年之间获得了 John Smith 记录，2011 年至 2012 年之间什么都没有，而 2013 年至 2014 年之间获得了 Alan Cooper 记录。

在我写这篇文章时，我的同事设法通过使用这个更明确的语句来修复它。

where election_date between  '01-JAN-2011' AND '01-JAN-2012'

但我还是想知道为什么上面的语句仍然运行得这么奇怪？也许我错过了几年使用 to_date 函数的东西？

Answer 1

你有没有看看你的to_date()实际产生的功能？

select to_date(2011, 'yyyy'), to_date(2012, 'yyyy'), to_date(2013, 'yyyy')
from dual;

TO_DATE(2011,'Y TO_DATE(2012,'Y TO_DATE(2013,'Y
--------------- --------------- ---------------
01-MAY-11       01-MAY-12       01-MAY-13

根据这些日期，您得到的结果是正确的。

如果您不指定月和日，则它们默认为当月的第一天，但​​时间默认为午夜。来自documentation for date time literals ，这是我能找到的关于这种行为的唯一官方引用:

If you specify a date value without a time component, then the default time is midnight (00:00:00 or 12:00:00 for 24-hour and 12-hour clock time, respectively). If you specify a date value without a date, then the default date is the first day of the current month.

如果你要使用

where election_date between  '01-JAN-2011' AND '01-JAN-2012'

...那么你仍然应该使用 to_date()因为您依赖于将来可能会更改的默认日期格式掩码，并且取决于 session 。 between也是包容性的，因此使用您的 2011 年 View 将包括 2012 年 1 月 1 日举行的选举，这可能不是您想要的，因此使用 >=和 <也许是安全的。或者使用日期文字:

where election_date >= date '2011-01-01' and election_date < date '2012-01-01'