Oracle to_date 季度格式函数

Question

我需要找到一些在不同季度创建的记录。例如，我正在查找 2008 年第 4 季度到 2010 年第 1 季度之间创建的所有记录。我的 WHERE 子句中有此内容：

...and r.record_create_date between to_date('2008 4','YYYY Q')
                                and to_date('2010 1','YYYY Q')

但 Oracle 说：ORA-01820: 格式代码不能出现在日期中输入格式。 Q 是一个有效的日期格式符号，所以我不确定发生了什么。这是否是查找日历季度之间的值的有效方法，或者是否有更好的方法？

---

如果我执行以下命令，也很有趣，并且可能相关：

select to_date('2009','YYYY') from dual;

我的 IDE 中显示的值是 2009-08-01。我预计2009-08-04，因为今天是2010-08-04。

这：

select to_date('2009 1','YYYY Q') from dual;

当然，失败了。

（甲骨文10g）

Answer 1

Oracle 说：ORA-01820：格式代码不能出现在日期输入格式中。 Q 是一个有效的日期格式符号，因此我不确定发生了什么。

请参阅http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004 .htm#i34948。转换为日期、时间戳等时，并非所有格式元素都被允许。

我建议不要使用 Between 进行日期范围检查。人们常常会在最后一天错过期望包含的价值观。所以我会翻译成

and r.record_create_date between to_date('2008 4','YYYY Q')
                             and to_date('2010 1','YYYY Q')

：

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.

Answer 2

有人在 OTN 上问了同样的问题：http://forums。 oracle.com/forums/thread.jspa?threadID=1081398&tstart=255

问题的关键在于您不能在TO_DATE 函数。

既然您已经指定了日期的一部分，为什么不提供整个日期呢？还要注意，to_date('2010 1','YYYY Q') 会给您 2010 年 1 月 1 日，而您真正想要的是 2010 年 3 月 31 日……午夜前一秒。

Answer 3

由于季度与月份之间的关系是一对多，因此执行 TO_DATE('2008 1', 'yyyy q'); 没有意义。应该返回什么日期？季度初、季度末……？ （另一方面，将日期转换为季度 - 如 TO_CHAR(SYSDATE, 'yyyy q') 是有意义的，因为特定日期仅存在于一个季度中。）

因此，如果您确实想要一个查找日期的查询，介于两个季度之间，您将必须“滚动自己的”（明确说明季度开始/结束的日期。）

作为旁注，如果有人考虑不使用 TO_DATE，请不要使用以下内容：WHERE date_value BETWEEN 'date string1' 和 'date string2' 之间，不带 TO_DATE 函数。它采用默认的日期格式，并且在某些情况下可以完全避免潜在有用的索引。

下面是一个示例，其中相同的查询可能有不同的结果。

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

（请注意，在第二个实例中，没有返回错误。它只是假设 0001 年 1 月 10 日和 0031 年 12 月 10 日。）

Answer 4

我认为最好的方法是只输入季度开始日期和季度结束日期，甚至不考虑 to_date。我认为如果你使用

between '1-Jan-10' and '31-Dec-10'

例如，那么你不需要（在Oracle中我相信）需要to_date并且它并不比输入季度数字困难多少

Answer 5

要在 Oracle 中根据年份和季度计算季度的第一天和季度的最后一天：
我使用事实

start_month= -2 + 3 * 季度

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