VARCHAR to DATE

[DugsDMan]

I need to import some data from an old Oracle table to anew one. In the old table, I have a field that contains a VARCHAR2(6) field. This field holds date data in MMYYYY format (I didn't design the original application). Now, I want to import it, but store it as a date. When I tried to do this using the to_date function, it appears to only store the last 2 numbers in the year. When I display it, 1998 will actually be 2098.

Does anyone know an easy way to convert the data from VARCHAR2 to DATE in an import? I'm sure someone does, I'm just starting to go in circles.

Thanks!

 

[carp]

Try:

SELECT to_date(my_six_char_field,'MMYYYY')
  FROM dual;

This will give you the correct year; each date will be for the 1st of the respective month.

Elbert, CO
1254 MST

 

[DugsDMan]

I've tried that, but when I pull the information up in an application and display the date in mm/yyyy format, it comes up as 01/2096, instead of 01/1996, like it's not even storing the "19" from the front of the year. It seems like a Y2K type issue (I know it's not, but that's the easiest way to describe it).

 

[ddrillich]

Can you please try -
SELECT to_date(my_six_char_field,'MMYYRR') FROM dual;

Regards,
Dan

 

[carp]

DugsDMan -
Could you post some samples of your VARCHAR2(6) data for us?
Also, have you tried storing the converted date values into a table, then doing a

SELECT to_char(date_column_name,'YYYY') FROM my_table;

?

If so, did you get the same results? Is it possible your application is at fault and not your data?

Elbert, CO
1640 MST

 

[DugsDMan]

When I run this query against my original field:
SELECT to_date(rtg_commit_strt_dt,'MMYYYY')
FROM db.table;


I get these results:
TO_DATE(RTG_COMMIT_S
--------------------
01-FEB-98
01-FEB-96
01-JAN-98
01-FEB-92
01-JUN-96
01-NOV-92
01-MAR-98
01-FEB-98
01-JAN-96
01-JAN-96
01-JAN-96
01-JAN-96
.....

Is there a way to display the 4-digit year? Sorry about all the questions, I'm just new to Oracle and getting frustrated :-(

 

[DugsDMan]

Oops, here a sample of the original data:
RTG_CO
------
021998
021996
011998
021992
061996
111992
031998
021998
011996
011996

 

[DugsDMan]

OK, I've tried this query: select to_char(rtg_commit_strt_dt, 'mm/dd/yyyy') from DB.Table;

And I get back:
TO_CHAR(RT
----------
02/01/2098
02/01/2096
01/01/2098
......

So, it's not the application. It must be something on Oracle. I have the field defined as DATE. Is it possibly only storing the last two digits, then trying to figure them out when I run a query? If so, how can I tell it in the table definition what to expect and store.

Thanks!

 

[carp]

You're right - it's not the application!
But Oracle is storing all four digits for the year; it's just getting the wrong century for some reason.

Here are the results of my experiment; try it on your system and see if it works the same way or not:

create table dug(rtg_co varchar2(6), rtg_date date);
Table created.

insert into dug values('021998',to_date('021998','MMYYYY'));
1 row created.

insert into dug values('021996',to_date('021996','MMYYYY'));
1 row created.

insert into dug values('011998',to_date('011998','MMYYYY'));
1 row created.

select rtg_co, to_char(rtg_date,'MM-DD-YYYY') from dug;

RTG_CO TO_CHAR(RT
------ ----------
021998 02-01-1998
021996 02-01-1996
011998 01-01-1998

Elbert, CO
0834 MST

 

[DugsDMan]

OK, not I'm really confused. When I run that, I get the same results you do. Why is the century right on yours, but not my original one?

I even tried only inserting the char field, then updating the date field by formatting the character field. That comes out fine as well.

This leads me to believe that something must wrong with my import script. I'm going to take a look at that and let you all know what I find.

Thanks for all the help!!!

 

[kevineva]

Dug,
I'm inferring (ok, guessing) a bit here, but it looks like in your import script, you are probably doing something like:
insert into bar(rtg_commit_start_dt)
select to_date(commit_date,'mmdyyyy') from foo;

when what you really want to do is:
insert into bar(rtg_commit_start_dt)
select to_date(commit_date,'mmyyyy') from foo;

Your selects are showing that, whatever format string you're using to perform the insert, it's only using the last two digits for the year info.