Oracle 在SYSDATE上的TO_DATE() 在插入所有查询中失败。

你好，



昨天我使用insert all query直接从toad在两个表中输入8行。当时我用TO_DATE(SYSDATE，'dd-MON-YYYY HH24:MI') 在我的表中插入sysdate (我知道sysdate是足够的，但因为过去四个月我在Oracle Apex上工作，我习惯了这个功能 & 我输入错误)。该查询已在生产中运行。今天，我正在检查这8行，我发现 '10-03-0019 '仅在我的表中插入了该特定列 (但必须14:10 10-03-2019)。你能详细说明为什么会这样吗？

这是我的查询

INSERT ALL
INTO        TABLE1
            (CREATION_DATE,
             CREATED_BY,
             LAST_UPDATED_DATE,
             LAST_UPDATED_BY,
             APPLICATION_ID,
             ENGINEER_ID,
             ENGINEER_NAME,
             EMPLOYEE_ID,
             END_DATE)
VALUES      (TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             ,
             ,
             ,
             ,
             NULL)
INTO        TABLE1
            (CREATION_DATE,
             CREATED_BY,
             LAST_UPDATED_DATE,
             LAST_UPDATED_BY,
             APPLICATION_ID,
             ENGINEER_ID,
             ENGINEER_NAME,
             EMPLOYEE_ID,
             END_DATE)
VALUES      (TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             ,
             ,
             ,
             ,
             NULL)
INTO        TABLE1
            (CREATION_DATE,
             CREATED_BY,
             LAST_UPDATED_DATE,
             LAST_UPDATED_BY,
             APPLICATION_ID,
             ENGINEER_ID,
             ENGINEER_NAME,
             EMPLOYEE_ID,
             END_DATE)
VALUES      (TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             ,
             ,
             ,
             ,
             NULL)
INTO        TABLE1
            (CREATION_DATE,
             CREATED_BY,
             LAST_UPDATED_DATE,
             LAST_UPDATED_BY,
             APPLICATION_ID,
             ENGINEER_ID,
             ENGINEER_NAME,
             EMPLOYEE_ID,
             END_DATE)
VALUES      (TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             ,
             ,
             ,
             ,
             NULL)
INTO        TABLE2
            (CREATION_DATE,
             CREATED_BY,
             LAST_UPDATED_DATE,
             LAST_UPDATED_BY,
             APPLICATION_ID,
             USER_ID,
             ROLE_ID,
             EMPLOYEE_ID,
             CUSTOMER_ID,
             VENDOR_ID)
VALUES      (TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             ,
             ,
             ,
             ,
             NULL,
             NULL
             )
INTO        TABLE2
            (CREATION_DATE,
             CREATED_BY,
             LAST_UPDATED_DATE,
             LAST_UPDATED_BY,
             APPLICATION_ID,
             USER_ID,
             ROLE_ID,
             EMPLOYEE_ID,
             CUSTOMER_ID,
             VENDOR_ID)
VALUES      (TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             ,
             ,
             ,
             ,
             NULL,
             NULL
             )
INTO        TABLE2
            (CREATION_DATE,
             CREATED_BY,
             LAST_UPDATED_DATE,
             LAST_UPDATED_BY,
             APPLICATION_ID,
             USER_ID,
             ROLE_ID,
             EMPLOYEE_ID,
             CUSTOMER_ID,
             VENDOR_ID)
VALUES      (TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             ,
             ,
             ,
             ,
             NULL,
             NULL
             )
INTO        TABLE2
            (CREATION_DATE,
             CREATED_BY,
             LAST_UPDATED_DATE,
             LAST_UPDATED_BY,
             APPLICATION_ID,
             USER_ID,
             ROLE_ID,
             EMPLOYEE_ID,
             CUSTOMER_ID,
             VENDOR_ID)
VALUES      (TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             ,
             ,
             ,
             ,
             ,
             NULL,
             NULL
             )
SELECT * FROM DUAL;

你有隐式转换正在进行!

事情是，SYSDATE已经返回一个日期。所以真正发生的是:

to_date ( to_char ( sysdate,  ) )

所以不同设置的客户端会得到不同的结果:

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; 

select to_date ( sysdate, 'DD-MON-YYYY HH24:MI:SS' ) from dual;

TO_DATE(SYSDATE,'DD-MON-YYYYHH24:MI:SS')   
11-MAR-2019 03:30:49 

alter session set nls_date_format = 'YYYY/MM/DD'; 

select to_date ( sysdate, 'DD-MON-YYYY HH24:MI:SS' ) from dual;

ORA-01861: literal does not match format string

抛弃日期。

永远不要约会!