Oracle SQL Date Format

Question

I have been trying to convert the following varchar into date format with no luck:

12/99
01/04

I have tried to_char(mydate, 'mm/yy') which gives me errors. Is there any way to convert this to date format even though I am missing the day?

So you have a DATE type, and you want to show the month,year only? — OldProgrammer
– OldProgrammer, Commented Jan 20, 2015 at 16:37
Yes, that is how it was entered in the Access db and I am trying to migrate the data to Oracle. I tried the to_char(to_date(mydate, 'mm/yy')). However some of the years are not correct, for example 99 ends up being 2099 instead of 1999. — Sam
– Sam, Commented Jan 20, 2015 at 16:40

Aramillo · Accepted Answer · 2015-01-20 16:45:49Z

5

You can use RR format for year:

select to_date('12/99','mm/rr') from dual --01/12/1999
select to_date('01/04','mm/rr') from dual --01/01/2004

This format get years between 1950 to 2049.

answered Jan 20, 2015 at 16:45

Aramillo

3,2263 gold badges27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

And when the current year is 2050, it will give you different results; 12/99 will give you December 1, 2099, and 01/04 will give you January 1, 2150.

If they're executing this same code in 2050, they'll have more serious problems then that.