SQL 具有四分之一格式的 Oracle to_date 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3408622/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle to_date function with quarter-format
提问by FrustratedWithFormsDesigner
I need to find some records created in a range of quarters. For example, I'm looking for all records created between the 4th quarter of 2008 and the 1st quarter of 2010. I have this in my WHERE-clause:
我需要找到在一系列季度中创建的一些记录。例如，我要查找在 2008 年第 4 季度和 2010 年第 1 季度之间创建的所有记录。我的 WHERE 子句中有这个：
...and r.record_create_date between to_date('2008 4','YYYY Q')
and to_date('2010 1','YYYY Q')
but Oracle says: ORA-01820: format code cannot appear in date input format. The Qis a valid date format symbol, so I'm not sure what's happened. Is this even a valid way to find values in between calender quarters, or is there a better way?
但甲骨文说：ORA-01820: format code cannot appear in date input format。这Q是一个有效的日期格式符号，所以我不确定发生了什么。这甚至是在日历季度之间找到值的有效方法，还是有更好的方法？
Also interesting, and possibly related, if I execute this:
如果我执行此操作，也很有趣，并且可能相关：
select to_date('2009','YYYY') from dual;
The value displayed in my IDE is 2009-08-01. I would have expected 2009-08-04, since todayis 2010-08-04.
我的 IDE 中显示的值为2009-08-01. 我早就料到了2009-08-04，既然是今天2010-08-04。
This:
这个：
select to_date('2009 1','YYYY Q') from dual;
of course, fails.
当然，失败。
(Oracle 10g)
（甲骨文 10g）
回答by Shannon Severance
Oracle says: ORA-01820: format code cannot appear in date input format. The Qis a valid date format symbol, so I'm not sure what's happened.
甲骨文说：ORA-01820: format code cannot appear in date input format。这Q是一个有效的日期格式符号，所以我不确定发生了什么。
See the second column of table 2.15 at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34948. Not all format elements are allowed when converting to dates, timestamps, etc.
请参阅http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34948 上的表 2.15 的第二列。转换为日期、时间戳等时，并非所有格式元素都被允许。
I recommend against using between for date range checks. People often will miss values within the ending day that the expect to be included. So I would translate:
我建议不要使用 between 进行日期范围检查。人们通常会错过期望包含的结束日内的值。所以我会翻译：
and r.record_create_date between to_date('2008 4','YYYY Q')
and to_date('2010 1','YYYY Q')
To
到
and to_date('2008-10-01', 'YYYY-MM-DD') <= r.record_create_date
and record_create_date < to_date('2010-04-01', 'YYYY-MM-DD') -- < beginning of 2Q2010.
回答by OMG Ponies
Someone asked the same question on OTN: http://forums.oracle.com/forums/thread.jspa?threadID=1081398&tstart=255
有人在 OTN 上问了同样的问题：http: //forums.oracle.com/forums/thread.jspa?threadID= 1081398&tstart= 255
The crux of the issue is that you can notspecify "Q" in the TO_DATE function.
问题的关键是您不能在 TO_DATE 函数中指定“Q”。
Given that you're already specifying a portion of the date, why not provide the entiredate? Mind too that to_date('2010 1','YYYY Q')would give you Jan 1st, 2010 when you really want March 31st, 2010... at a second to midnight.
鉴于您已经指定了日期的一部分，为什么不提供整个日期？也请注意to_date('2010 1','YYYY Q')，当您真正想要 2010 年 3 月 31 日时，它会给您 2010 年 1 月 1 日……在午夜之前。
回答by Patrick Marchand
Since the relationship between quarters to months is one-to-many, it doesn't make sense to do TO_DATE('2008 1', 'yyyy q'); what date should be returned? The first of the quarter, the end of the quarter, ...? (On the other hand, converting a date to a quarter - like TO_CHAR(SYSDATE, 'yyyy q') makes sense because a specific date only exists in one quarter.)
由于季度到月份的关系是一对多的，所以做 TO_DATE('2008 1', 'yyyy q'); 没有意义。应该返回什么日期？季初，季末，……？（另一方面，将日期转换为季度 - 像 TO_CHAR(SYSDATE, 'yyyy q') 是有道理的，因为特定日期只存在于一个季度。）
So, if you do want a query that looks for a date that falls between two quarters, you will have to "rolll your own" (explicitly stating the dates of the start/end of a quarter.)
因此，如果您确实想要一个查找介于两个季度之间的日期的查询，您将不得不“滚动您自己的”（明确说明一个季度的开始/结束日期。）
As a side note, in case anyone is considering not using TO_DATE please do not use things like: WHERE date_value BETWEEN 'date string1' and 'date string2' without the TO_DATE function. It assumes a default date format and under certain situations can avoid potentially useful indexes altogether.
作为旁注，如果有人考虑不使用 TO_DATE，请不要使用以下内容： WHERE date_value BETWEEN 'date string1' 和 'date string2' 没有 TO_DATE 函数。它采用默认日期格式，并且在某些情况下可以完全避免可能有用的索引。
Below is one example where the same query can have a different result.
下面是一个示例，其中相同的查询可能有不同的结果。
select sysdate from dual where sysdate between '1-Jan-10' and '31-Dec-10';
SYSDATE
---------
04-AUG-10
SQL> alter session set nls_date_format = 'YYYY-MM-DD';
Session altered.
SQL> select * from dual where sysdate between '1-Jan-10' and '31-Dec-10';
no rows selected
(Notice that in the second instance no error is returned. It just assumes Jan 10, 0001 and Dec. 10th, 0031.)
（请注意，在第二个实例中没有返回错误。它只是假设 0001 年 1 月 10 日和 0031 年 12 月 10 日。）
回答by alvalongo
To calculate in Oracle the first day of a quarter and the last day of a quarter from the year and quarter: I Use the fact
在 Oracle 中计算季度的第一天和季度的最后一天：我使用事实
start_month= -2 + 3 * quarter
start_month= -2 + 3 * 季度
last_month = 3 * quarter
last_month = 3 * 季度
variable v_year number
variable v_quarter number
exec :v_year :=2017
exec :v_quarter:=4
select :v_year as year,
:v_quarter as quarter,
to_date(:v_year||to_char(-2+3*:v_quarter,'fm00'),'yyyymm') as quarter_start,
last_day(to_date(:v_year||to_char(3*:v_quarter,'fm00')||'01 23:59:59','yyyymmdd hh24:mi:ss')) as quarter_end
from dual a;
YEAR|QUARTER|QUARTER_START |QUARTER_END
2017| 4|2017-10-01 00:00:00|2017-12-31 23:59:59
回答by jle
I think the best way is to just input the quarter start date and quarter end dates without even bothering with to_date. I think if you use
我认为最好的方法是只输入季度开始日期和季度结束日期，甚至不用考虑 to_date。我想如果你使用
between '1-Jan-10' and '31-Dec-10'
for example, then you don't (in Oracle I believe) need to_date and it isn't much more difficult than typing in the quarter number
例如，那么您（在 Oracle 中我相信）不需要 to_date 并且它并不比输入季度数字困难多少
