Problem with inserting dates

[kalyson]

I am using a variable of type: DATE

Our instance of Oracle has: NLS_DATE_FORMAT DD-MON-YY

I select a DATE from table A into this date variable. I then insert that variable into another table, B. Dates that were before 2000 change -- like 1995 becomes 2095. I do nothing to the date between select and insert.

My PL/SQL is being executed in Toad version 9.1. Oracle is release 1002000300

Does anyone know what is wrong?

Thanks

[amitpatel66]

I am using a variable of type: DATE

Our instance of Oracle has: NLS_DATE_FORMAT DD-MON-YY

I select a DATE from table A into this date variable. I then insert that variable into another table, B. Dates that were before 2000 change -- like 1995 becomes 2095. I do nothing to the date between select and insert.

My PL/SQL is being executed in Toad version 9.1. Oracle is release 1002000300

Does anyone know what is wrong?

Thanks

That shouldnt be the case.
Can you try the below query and then execute your procedure:

 
ALTER SESSION SET nls_date_format = 'DD-MON-RR'
 
 

[kalyson]

I'll admit to being a newbie with PL/SQL, but I did already try that. I got a syntax error. Maybe I should put it outside the PL/SQL procedure...

[kalyson]

Well, I ran the alter session outside the procedure, then ran the procedure and same thing happens. I print out the date variable just prior to insertion, and the dates are like, 1997, 1995. Then in the table I see they have been inserted as 2097, 2095. Very strange.

[kalyson]

I found in Toad where to make the NLS parameter setting. It still does the same thing...Geeezzz.

[amitpatel66]

Did you try this:

 
ALTER SESSION SET nls_date_format = 'DD-MON-RRRR'
 
 

With 4 R's ?

[amitpatel66]

Can you post your code that you are using for INSERTING data from table A to table B?

[Saii]

Just try using
to_date(to_char(<your date variable>,'DD-MON-YYYY'),'DD-MON-YYYY') while inserting to other table

[Mala232]

I am using a variable of type: DATE

Our instance of Oracle has: NLS_DATE_FORMAT DD-MON-YY

I select a DATE from table A into this date variable. I then insert that variable into another table, B. Dates that were before 2000 change -- like 1995 becomes 2095. I do nothing to the date between select and insert.

My PL/SQL is being executed in Toad version 9.1. Oracle is release 1002000300

Does anyone know what is wrong?

Thanks

hi ,

try this

SQL> alter session set NLS_DATE_FORMAT = 'DD-MM-RRRR';

and try to insert the date values by converting it to

insert into table_name values ( to_date( <date column>,' RRRR-MM-DD HH:MI:SS') );

[kalyson]

Just try using
to_date(to_char(<your date variable>,'DD-MON-YYYY'),'DD-MON-YYYY') while inserting to other table

Thanks, but that did not work. I had already tried it a while back, but it was a good suggestion.

[kalyson]

hi ,

try this

SQL> alter session set NLS_DATE_FORMAT = 'DD-MM-RRRR';

and try to insert the date values by converting it to

insert into table_name values ( to_date( <date column>,' RRRR-MM-DD HH:MI:SS') );

Oracle gives an error for this, unfortunately.

[kalyson]

Originally Posted by Saii
Just try using
to_date(to_char(<your date variable>,'DD-MON-YYYY'),'DD-MON-YYYY') while inserting to other table

Thanks, but that did not work. I had already tried it a while back, but it was a good suggestion.

I figured out the solution, this one was close, but it was actually this:

to_char(<your date variable>,'DD-MON-YYYY') but with quotes around it. Have to include the '' around it, too. Just the to_char by itself with formatting did the trick!

Thanks!

[Saii]

I thought you need a date datatype column.Ignoring to_date conversion,results in varchar2 column, if this suits your requirement, then you are good to go :)