Search code examples
sqloracleoracle-sqldeveloperdate-formattingdml

SYSDATE is implicitly is getting converted to to_date() in SQL developer DML Export


I'm trying to export the dml from sql developer(v 17.4) but the date column is not coming as expected.

While inserting the record i used SYSDATE for date field but i think implicitly the sql developer is converting that SYSDATE to to_date(). Here's the Query I'm using to insert-

Insert into table_name (INT_OBJ_ID,INT_OBJ_NM,CREATE_DT,CREATE_USER_ID,UPDATE_DT,UPDATE_USER_ID) values (2016,'RDS_BV_RETURN_MAIL_INFO',SYSDATE,'User01',SYSDATE,'User01');

But in DML Export i'm getting this -

Insert into table_name (INT_OBJ_ID,INT_OBJ_NM,CREATE_DT,CREATE_USER_ID,UPDATE_DT,UPDATE_USER_ID) values (2016,'RDS_BV_RETURN_MAIL_INFO',to_date('09-AUG-23','DD-MON-RR'),'User01',to_date('09-AUG-23','DD-MON-RR'),'User01');

Expexted Export : Insert into table_name (INT_OBJ_ID,INT_OBJ_NM,CREATE_DT,CREATE_USER_ID,UPDATE_DT,UPDATE_USER_ID) values (2016,'RDS_BV_RETURN_MAIL_INFO',SYSDATE,'User01',SYSDATE ,'User01');

Does anyone know how can we resolve this?

[My Export wizard screenshots(incase it helps)](https://i.sstatic.net/C0Lja.png)

I tried to find the setting to default implicit conversion but couldn't find any. Even Tried to give SYSDATE in NLS but it expects valid date formate.


Solution

  • Export extract what it found in that column. It didn't find SYSDATE (as function name), but value returned by SYSDATE function at the moment of insert and it was - apparently, for that row - 9th of August 2023.

    Export (actually, people who programmed it) chose to use to_date function with appropriate format mask: to_date('09-AUG-23', 'DD-MON-RR').

    Though, I'd rather see something different, such as to_date('09.08.2023', 'dd.mm.yyyy'). You certainly wouldn't be able to insert "AUG" into my database which speaks Croatian - of course, not without additional settings (altering the session to set format model and language).

    I don't think you can get SYSDATE in export. But, what you can do is to omit date column(s) from export: in "Specify Data" step, don't include all columns (*) - click the pencil button and uncheck date column(s). Then, on target side, either set column's default value to sysdate, e.g.

    alter table target_table modify date_column default sysdate;
    

    or populate it manually after insert, e.g.

    update target_table set date_column = sysdate where date_column is null;