Oracle根据年份返回不一致的结果选择日期 [英] Oracle select dates based on year returning inconsistent results
问题描述
我已经修改了一个Oracle视图的特殊问题。这个想法是，我有一个基础表，存储所有选举候选人，并且还有一个选举日期栏，告诉我们这个候选人是哪一个选举，就像这样。
I'm getting a peculiar problem with an Oracle view I've modified. The idea is I have a base table that stores all election candidates and also has an election date column, telling us what election this candidate was part of, like so.
NAME | ELECTION_DATE
---------------------------
John Smith | 01-APR-2011
Alan Cooper| 02-MAY-2013
ELECTION_DATE列存储为日期。我们使用2011年的2011年和2013年版本，构建了2个视图。
The ELECTION_DATE column is stored as a date. We constructed 2 views for 2011 and 2013, using the following statment for 2011.
where election_date >= to_date(2011,'yyyy') and election_date < to_date(2012,'yyyy')
我们以为会拿起上面的John Smith纪录，但没有。特别的是，当我们在2013年的视图中这样做时。
Which we thought would pick up just the John Smith record above, but it didn't. The peculiar thing was that when we did this for the 2013 view.
where election_date >= to_date(2013,'YYYY') and election_date < to_date(2014,'YYYY')
它只需要Alan Cooper记录。更特别的是，如果我们在2011年的视图中这样做。
It DID get just the Alan Cooper record. Even more peculiar is that if we did this for the 2011 view.
where election_date >= to_date(2010,'yyyy') and election_date < to_date(2011,'yyyy')
我们只收录了约翰·史密斯的纪录，而不是艾伦·库珀纪录，这是我们想要的，但不是我们的预期。
We got just the John Smith record and not the Alan Cooper record, which is what we wanted but not what we expected.
然后，我们将它们更改为两个语句之间，它们的行为完全相同，即在2010年至2011年之间，获得了约翰·史密斯的纪录，2011年至2012年之间没有什么，2013年和2014年获得了Alan Cooper的记录。
We then changed them both to between statements and they behaved exactly the same i.e. between 2010 and 2011 got the John Smith record, between 2011 and 2012 got nothing and between 2013 and 2014 got the Alan Cooper record.
在我写这篇文章的同时，我的同事用这个更明确的陈述来解决它。
Whilst I was writing this my colleague managed to fix it by using this more explicit statement.
where election_date between '01-JAN-2011' AND '01-JAN-2012'
但是我仍然想知道为什么上述语句仍然工作如此奇怪？我不知道在几年内使用to_date函数吗？
But I still want to know why the above statements still worked so strangely? Something I am missing about using to_date functions for just years maybe?
推荐答案
你看过你的 to_date（）实际生成的函数
Did you look at what your to_date() functions actually produced?
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
根据这些日期，您获得的结果是正确的。
Based on those dates the results you got are correct.
如果您不要指定月份和日期，因此默认为当月的第一天，但时间默认为午夜。从日期时间文字的文字，这是我可以找到的唯一官方参考：
If you don't specify the month and day then they default to the first day of the current month, although the time defaults to midnight. From the documentation for date time literals, which is the only official reference I can find for this behaviour:
如果您指定一个没有时间组件的日期值，那么默认的
时间是午夜（24小时的00:00:00或12:00:00） 12分钟时间分别为
）如果您指定的日期值没有日期，则
的默认日期是当月的第一天。
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（）因为您依赖于将来可能更改的默认日期格式掩码，并且与会话有关。 之间的也是包容性的，所以使用你的2011年观点将包括2012年1月1日举行的选举，这可能不是你想要的，所以使用> = 和
... then you should still use to_date() as you're relying on a default date format mask that might change in the future, and is session dependent. between is also inclusive, so using that your 2011 view would include an election held on 01-Jan-2012, which probably isn't what you want, so using >= and < is maybe safe. Alternatively use date literals:
where election_date >= date '2011-01-01' and election_date < date '2012-01-01'
这篇关于Oracle根据年份返回不一致的结果选择日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
