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
Advertisement
Answer
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