Oracle date to string conversion

Question

I have a string column COL1 when I am doing this

SELECT TO_CHAR(TO_DATE(COL1,'dd-mon-yy'), 'mm/dd/yyyy')
FROM TABLE1

The data in COL1 is in dd-mon-yy, eg: 27-11-89 and 89 is 1989 but the select returns it as 11/27/2089.

I have to do an inner TO_DATE because if I don't then I am getting an invalid number error (ORA-01722: invalid number)

How can show 1989 instead of 2089? Please help

Answer 1

The data in COL1 is in dd-mon-yy

No it's not. A DATE column does not have any format. It is only converted (implicitely) to that representation by your SQL client when you display it.

If COL1 is really a DATE column using to_date() on it is useless because to_date() converts a string to a DATE.

You only need to_char(), nothing else:

SELECT TO_CHAR(col1, 'mm/dd/yyyy') 
FROM TABLE1

What happens in your case is that calling to_date() converts the DATE into a character value (applying the default NLS format) and then converting that back to a DATE. Due to this double implicit conversion some information is lost on the way.

---

Edit

So you did make that big mistake to store a DATE in a character column. And that's why you get the problems now.

The best (and to be honest: only sensible) solution is to convert that column to a DATE. Then you can convert the values to any rerpresentation that you want without worrying about implicit data type conversion.

But most probably the answer is "I inherited this model, I have to cope with it" (it always is, apparently no one ever is responsible for choosing the wrong datatype), then you need to use RR instead of YY:

SELECT TO_CHAR(TO_DATE(COL1,'dd-mm-rr'), 'mm/dd/yyyy')
FROM TABLE1

should do the trick. Note that I also changed mon to mm as your example is 27-11-89 which has a number for the month, not an "word" (like NOV)

For more details see the manual: http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00215