Oracle date to string conversion
oracle, sql
Solution
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
Problem
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