当使用to_date(,'yyyy')把文本转为日期时,会默认使用当前的月份
SQL> select sysdate from dual;
SYSDATE
-----------
2014-12-15
1 row selected
SQL> select to_date('2013','yyyy') from dual;
TO_DATE('2013','YYYY')
----------------------
2013-12-01
1 row selected
可以看到,当前月份为12月时,to_date得到的结果就是13年12月。所以如果你想得到1月份,就需要转换下
SQL> select to_date('2013' || '-01','yyyy-mm') from dual;
TO_DATE('2013'||'-01','YYYY-MM
------------------------------
2013-01-01
1 row selected
或
SQL> select trunc(to_date('2013','yyyy'),'yyyy') from dual;
TRUNC(TO_DATE('2013','YYYY'),'
------------------------------
2013-01-01
1 row selected
我有一个取季度的例子,就因这一点发生了错误
SQL> SELECT sn AS 季度,
2 (sn - 1) * 3 + 1 AS 开始月份,
3 add_months(to_date(年, 'yyyy'), (sn - 1) * 3) AS 开始日期,
4 add_months(to_date(年, 'yyyy'), sn * 3) - 1 AS 结束日期
5 FROM (SELECT '2013' AS 年, LEVEL AS sn FROM dual CONNECT BY LEVEL <= 4);
季度 开始月份 开始日期 结束日期
---------- ---------- ----------- -----------
1 1 2013-12-01 2014-02-28
2 4 2014-03-01 2014-05-31
3 7 2014-06-01 2014-08-31
4 10 2014-09-01 2014-11-30
4 rows selected
正确的写法应该是
SQL> SELECT sn AS 季度,
2 (sn - 1) * 3 + 1 AS 开始月份,
3 add_months(年初, (sn - 1) * 3) AS 开始日期,
4 add_months(年初, sn * 3) - 1 AS 结束日期
5 FROM (SELECT trunc(to_date(年, 'yyyy'), 'yyyy') AS 年初, sn
6 FROM (SELECT '2013' AS 年, LEVEL AS sn
7 FROM dual
8 CONNECT BY LEVEL <= 4) a) b;
季度 开始月份 开始日期 结束日期
---------- ---------- ----------- -----------
1 1 2013-01-01 2013-03-31
2 4 2013-04-01 2013-06-30
3 7 2013-07-01 2013-09-30
4 10 2013-10-01 2013-12-31
4 rows selected
这一点曾在课上对学生强调过,结果自已的书里还出现这种错误,实在是不应该,向各位读者抱歉