Skip to Main Content
  • Questions

  • TO_DATE() on SYSDATE failed in insert all query.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Anand.

Asked: March 11, 2019 - 5:41 am UTC

Last updated: March 11, 2019 - 10:31 am UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

Hello,



Yesterday I used insert all query to enter 8 rows in two tables directly from toad. At that time I used TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI') for inserting sysdate in my table (I know sysdate was enough but 'coz of since last four months I was working on Oracle Apex I get used to with that function & I typed it by mistakenly). That query was ran on production. Today I was checking those 8 rows & I found that '10-03-0019' was inserted only in that particular column (but it has to be 10-03-2019 14:10) in my table. Can you please elaborate why this happened?

Here's my query


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'),
             <SOME_VALUE>,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             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'),
             <SOME_VALUE>,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             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'),
             <SOME_VALUE>,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             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'),
             <SOME_VALUE>,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             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'),
             <SOME_VALUE>,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             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'),
             <SOME_VALUE>,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             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'),
             <SOME_VALUE>,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             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'),
             <SOME_VALUE>,
             TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI'),
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             <SOME_VALUE>,
             NULL,
             NULL
             )
SELECT * FROM DUAL;

and Chris said...

You have implicit conversions going on!

Thing is, SYSDATE already returns a date. So what really happens is :

to_date ( to_char ( sysdate, <nls format> ) )


So clients with different settings will get different results:

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


Ditch the TO_DATE.

Never, ever to_date a date!


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Default NLS_DATE_FORMAT

Rajeshwaran, Jeyabal, March 12, 2019 - 10:13 am UTC

Yes it is due to implicit conversions.

since the default nls_date_format = DD-MON-RR applying that to the implicit conversion it goes like this.

demo@PDB1> select sysdate from dual;

SYSDATE
-----------
12-MAR-2019

demo@PDB1> select to_date( to_char(sysdate,'dd-mon-rr') ,'dd-mon-yyyy')  from dual ;

TO_DATE(TO_
-----------
12-MAR-0019

demo@PDB1>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.