Problem with inserting dates

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

re: Problem with inserting dates

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

re: Problem with inserting dates

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...

re: Problem with inserting dates

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.

re: Problem with inserting dates

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

re: Problem with inserting dates

Did you try this:

With 4 R's ?

re: Problem with inserting dates

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

re: Problem with inserting dates

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

re: Problem with inserting dates

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') );

re: Problem with inserting dates

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

re: Problem with inserting dates

Oracle gives an error for this, unfortunately.

re: Problem with inserting dates

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


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!

re: Problem with inserting dates

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 :)