Oracle如何显示公元前日期

DBA闲思杂想录 2023-02-17

1318

本文出自[公众号]：DBA闲思杂想录

在Oracle数据库中，我们遇到的日期时间绝大部分是公元后的日期时间，那么如何显示公元前的日期时间呢？这种需求非常少见，相信一些同学都不清楚如何输出公元前日期时间，下面简单的介绍一下。

如果你见到下面TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')这种设置日期格式的形式，请不要奇怪。虽然我们平时可能都是使用'YYYY-MM-DD HH24:MI:SS'这个格式形式，但是这种TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')格式也是合法的日期格式，只是不常见而已。

SQL> SELECT TO_DATE('2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AS DATE_TIME_FRT FROM DUAL;

DATE_TIME_FRT
-------------------
2024-02-01 00:00:00

1 row selected.

SQL> SELECT TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AS DATE_TIME_FRT FROM DUAL;

DATE_TIME_FRT
-------------------
2024-02-01 00:00:00

1 row selected.

SQL>

复制

初看上面脚本，似乎SYYYY跟YYYY没有啥区别，那么这个S到底代表啥意思呢？其实S前缀指定如果遇公元前的日期，会在日期前加(-),也就是说如果时间是公元前的时间，那么这种格式会将公元前的时间加上一个“-”，下面我们来演示一下：

--实验脚本
SQL> CREATE TABLE TEST(COL_TIME DATE);

Table created.

SQL> INSERT INTO TEST 
  2  SELECT TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') FROM DUAL;

1 row created.

SQL> 
SQL> 
SQL> INSERT INTO TEST 
  2  SELECT TO_DATE('-0100-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') FROM DUAL;

1 row created.

SQL> commit;

复制

如果想插入公元前的日期，如果不设置为SYYYY，就会遇到ORA-01841: (full) year must be between -4713 and +9999, and not be 0这个错误，如下所示：

SQL> INSERT INTO TEST 
  2  SELECT TO_DATE('-0100-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_DATE('-0100-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
               *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

复制

测试如下所示：

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> SELECT * FROM TEST;

COL_TIME
-------------------
2024-02-01 00:00:00
0100-02-01 00:00:00

2 rows selected.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='SYYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> SELECT * FROM TEST;

COL_TIME
--------------------
 2024-02-01 00:00:00
-0100-02-01 00:00:00

2 rows selected.

复制

格式化时，如何将公元前的日期输出，如下所示,也是在格式化时，使用SYYYY-MM-DD HH24:MI:SS这种格式。

SQL> SELECT TO_CHAR(COL_TIME, 'YYYY-MM-DD HH24:MI:SS') FROM TEST;

TO_CHAR(COL_TIME,'Y
-------------------
2024-02-01 00:00:00
0100-02-01 00:00:00

2 rows selected.

SQL> SELECT TO_CHAR(COL_TIME, 'SYYYY-MM-DD HH24:MI:SS') FROM TEST;

TO_CHAR(COL_TIME,'SY
--------------------
 2024-02-01 00:00:00
-0100-02-01 00:00:00

2 rows selected.
复制