[20241118]invalid date 0000-00-00(zero year)的输入.txt

来源：这里教程网时间：2026-03-03 20:55:33 作者：

[20241118]invalid date 0000-00-00(zero year)的输入.txt --//昨天看了链接：https://www.anbob.com/archives/8511.html，类似的问题以前我也遇到过，我记忆里第一次在toad下，如果在浏 --//览数据模式，点击显示0000-00-00的grid会报错，提示invalid date。 --//我一直不明白这些日期输入如何输入的，在sqlplus下无法输入。最后只能认为一个程序OCI接受这些输入。 --//不过作者提供在sqlplu输入的方式，测试看看。 1.环境： SCOTT@book01p> @ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 21.0.0.0.0 BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.测试： SCOTT@book01p> create table test2(ct date); Table created. SCOTT@book01p> insert into test2 values (0); insert into test2 values (0) * ERROR at line 1: ORA-00932: inconsistent datatypes: expected DATE got NUMBER SCOTT@book01p> insert into test2 values(to_date('00000000','yyyymmdd')); insert into test2 values(to_date('00000000','yyyymmdd')) * ERROR at line 1: ORA-01843: not a valid month SCOTT@book01p> select to_date('0000-00-00','yyyy-mm-dd'); select to_date('0000-00-00','yyyy-mm-dd') * ERROR at line 1: ORA-00923: FROM keyword not found where expected --//与我以前测试类似，sqlplus下无法插入0000-00-00日期。 --//按照日期的转换格式计算RAW换算方法，'0000-00-00 00:00:00'拼接RAW 后是100,100,0,0,1,1,1，转换为16进制是64640000010101。 WITH FUNCTION display_raw(rawval RAW, type VARCHAR2) RETURN VARCHAR2 IS cn NUMBER; cv VARCHAR2(128); cd DATE; cnv NVARCHAR2(128); cr ROWID; cc CHAR(128); BEGIN IF (type = 'NUMBER') THEN dbms_stats.convert_raw_value(rawval, cn); RETURN to_char(cn); ELSIF (type = 'VARCHAR2') THEN dbms_stats.convert_raw_value(rawval, cv); RETURN to_char(cv); ELSIF (type = 'DATE') THEN dbms_stats.convert_raw_value(rawval, cd); RETURN to_char(cd); ELSIF (type = 'NVARCHAR2') THEN dbms_stats.convert_raw_value(rawval, cnv); RETURN to_char(cnv); ELSIF (type = 'ROWID') THEN dbms_stats.convert_raw_value(rawval, cr); RETURN to_char(cr); ELSIF (type = 'VARCHAR2') THEN dbms_stats.convert_raw_value(rawval, cc); RETURN to_char(cc); ELSE RETURN 'UNKNOWN DATATYPE'; END IF; END; select display_raw('64640000010101','DATE') x from dual; / X ------------------------------ 0000-00-00 00:00:00 WITH FUNCTION display_raw(rawval RAW, type VARCHAR2) RETURN VARCHAR2 IS cn NUMBER; cv VARCHAR2(128); cd DATE; cnv NVARCHAR2(128); cr ROWID; cc CHAR(128); BEGIN IF (type = 'NUMBER') THEN dbms_stats.convert_raw_value(rawval, cn); RETURN to_char(cn); ELSIF (type = 'VARCHAR2') THEN dbms_stats.convert_raw_value(rawval, cv); RETURN to_char(cv); ELSIF (type = 'DATE') THEN dbms_stats.convert_raw_value(rawval, cd); RETURN to_char(cd); ELSIF (type = 'NVARCHAR2') THEN dbms_stats.convert_raw_value(rawval, cnv); RETURN to_char(cnv); ELSIF (type = 'ROWID') THEN dbms_stats.convert_raw_value(rawval, cr); RETURN to_char(cr); ELSIF (type = 'VARCHAR2') THEN dbms_stats.convert_raw_value(rawval, cc); RETURN to_char(cc); ELSE RETURN 'UNKNOWN DATATYPE'; END IF; END; insert into test2 select display_raw('64640000010101','DATE') x from dual; / insert into test2 select display_raw('64640000010101','DATE') x from dual * ERROR at line 34: ORA-00928: missing SELECT keyword --//oracle不支持这样写法,不知道写错在哪里。 create or replace function stats_raw_to_date (p_in raw) return date is v_date date; v_char varchar2(25); begin dbms_stats.CONVERT_RAW_VALUE(p_in, v_date); return v_date; exception when others then return null; end; / SCOTT@book01p> insert into test2 select stats_raw_to_date('64640000010101') x from dual; 1 row created. SCOTT@book01p> commit ; Commit complete. SCOTT@book01p> select * from test2; CT ------------------- 2024-11-18 09:58:22 0000-00-00 00:00:00 --//21c也提供dbms_stats.CONVERT_RAW_TO_DATE函数。 SCOTT@book01p> @ desc_proc sys dbms_stats CONVERT_RAW_to_% INPUT OWNER PACKAGE_NAME OBJECT_NAME sample : @desc_proc sys dbms_stats gather_%_stats OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DEFAULTED ---------- ------------ ------------------------------ ---------- ------------- ------------- --------- ---------- SYS DBMS_STATS CONVERT_RAW_TO_VARCHAR2 1 VARCHAR2 OUT N 2 RAWVAL RAW IN N CONVERT_RAW_TO_DATE 1 DATE OUT N 2 RAWVAL RAW IN N CONVERT_RAW_TO_NUMBER 1 NUMBER OUT N 2 RAWVAL RAW IN N CONVERT_RAW_TO_BIN_FLOAT 1 BINARY_FLOAT OUT N 2 RAWVAL RAW IN N CONVERT_RAW_TO_BIN_DOUBLE 1 BINARY_DOUBLE OUT N 2 RAWVAL RAW IN N CONVERT_RAW_TO_NVARCHAR 1 NVARCHAR2 OUT N 2 RAWVAL RAW IN N CONVERT_RAW_TO_ROWID 1 ROWID OUT N 2 RAWVAL RAW IN N 14 rows selected. --//有了这些函数方便多了，以前11g版本仅仅有dbms_stats.convert_raw_value，而且返回值保存在第2个参数里面。 --//于是才有了上面的函数display_raw。 SCOTT@book01p> select dbms_stats.CONVERT_RAW_TO_DATE('64640000010101') from dual ; DBMS_STATS.CONVERT_ ------------------- 0000-00-00 00:00:00 SCOTT@book01p> insert into test2 select dbms_stats.CONVERT_RAW_TO_DATE('64640000010101') from dual ; 1 row created. SCOTT@book01p> commit ; Commit complete. SCOTT@book01p> select * from test2 where ct=dbms_stats.CONVERT_RAW_TO_DATE('64640000010101'); CT ------------------- 0000-00-00 00:00:00 0000-00-00 00:00:00 --//顺便提一下目前的toad 12.7版本，显示的是 0001/1/1. select dump(ct,16) c10 ,test2.* from test2 C10 CT -------------------------------- --------------------- Typ=12 Len=7: 78,7c,b,12,a,3b,17 2024-11-18 09:58:22 Typ=12 Len=7: 64,64,0,0,1,1,1 0001-01-01 00:00:00 Typ=12 Len=7: 64,64,0,0,1,1,1 0001-01-01 00:00:00 已选择 3 行。