1

Good day,

within sqlcl I will like to run a query that build a insert statement. Such as:

SELECT /*insert*/* FROM TABLE;

but each date value is returned as to_timestamp('date', 'format') and I want to get to_date('date', 'format') instead.

What do I need to set to make this thing happen?

Thanks!!

Editted:

For Example... I have the table datesaving with two columns id, and date. If I run this query

SELECT /*insert*/ id,date FROM datesaving ;

I get

Insert into DATASAVING (ID, DATE) values (1,to_timestamp('2016-02-15 11:05:07','RRRR-MM-DD HH24:MI:SS'));

and I want to get this instead

Insert into DATASAVING (ID, DATE) values (1,to_date('2016-02-15 11:05:07','RRRR-MM-DD HH24:MI:SS'));

I know i can change de nls_format, but I dont know hot to change this.

Eduardo Zamora
  • 99
  • 2
  • 5
  • 1
    Sorry this is not clear. Please explain what commands you are running and post what output you get (the whole thing or at least enough to provide proper context). – APC Jan 23 '18 at 08:26
  • I edited my question, hope it clarifies. – Eduardo Zamora Jan 24 '18 at 04:26

2 Answers2

0

This is because the metadata we in sqlcl are getting back tells us a DATE column is a timestamp so the code treats it as such. However, it will work just fine.

SQL> create table test_insert(id number,dt date,ts timestamp);

Table TEST_INSERT created.

SQL> insert into test_insert values(1,sysdate,systimestamp);

1 row inserted.

SQL> set sqlformat insert
SQL> select * from test_insert;
REM INSERTING into TEST_INSERT
SET DEFINE OFF;
Insert into TEST_INSERT (ID,DT,TS) values (1,to_timestamp('02-FEB-18','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('02-FEB-18 01.47.57.017643000 PM','DD-MON-RR HH.MI.SSXFF AM'));

<<<copy pasted to run the output >>>

SQL> insert into TEST_INSERT (ID,DT,TS) values (1,to_timestamp('02-FEB-18','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('02-FEB-18 01.47.57.017643000 PM','DD-MON-RR HH.MI.SSXFF AM'));

1 row inserted.
Kris Rice
  • 3,300
  • 15
  • 33
0

I found the trick to get the missing time part of the DATE type in this topic.

Just type the following command BEFORE typing your SELECT statements:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH.MI.SS AM';

The INSERT statements will take this format into account:

SQL> insert into TEST_INSERT (ID,DT,TS) values (1,to_timestamp('02-02-18 01.47.57','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('02-FEB-18 01.47.57.017643000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Note 1

I took the DD-MON-RR HH.MI.SSXFF AM format as it was the generated one of your example (without XFF, see below).

In my case, I have been given the DD/MM/RR HH24:MI:SS format. This shows the generated format for INSERT statements depends on NLS (National Language Support, i.e. locale) parameters (like NLS_TERRITORY) that should be set first to guaranty the portability of your code.

Use SELECT * FROM NLS_SESSION_PARAMETERS; to get the content of your parameters and set them at the beginning of your script.

Note 2

According to the given format, you must remove the XFF part at the end which does not make sense with a DATE format because the one doesn't include fractional seconds. So DD-MON-RR HH.MI.SSXFF AM becomes DD-MON-RR HH.MI.SS AM.

jbaptperez
  • 656
  • 6
  • 20