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

Quote:

Originally Posted by 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

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

Expand|Select|Wrap|Line Numbers

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:

Expand|Select|Wrap|Line Numbers

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

Quote:

Originally Posted by 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

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

Quote:

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.

kalyson

Quote:

Originally Posted by Mala232

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

Quote:

Originally Posted by kalyson

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