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

Answer 2

Try this. Oracle has this feature to distinguish the millennium years..

As you mentioned, if your column is a varchar, then the below query will yield you 1989..

select to_date(column_name,'dd/mm/rr') from table1;

When the format rr is used in year, the following would be done by oracle.

if rr->00 to 49 ---> result will be 2000 - 2049, if rr->50 to 99 ---> result will be 1950 - 1999

Answer 3

If your column is of type DATE (as you say), then you don't need to convert it into a string first (in fact you would convert it implicitly to a string first, then explicitly to a date and again explicitly to a string):

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

The date format your seeing for your column is an artifact of the tool your using (TOAD, SQL Developer etc.) and it's language settings.

Answer 4

Another thing to notice is you are trying to convert a date in mm/dd/yyyy but if you have any plans of comparing this converted date to some other date then make sure to convert it in yyyy-mm-dd format only since to_char literally converts it into a string and with any other format we will get undesired result. For any more explanation follow this: Comparing Dates in Oracle SQL

Answer 5

SELECT * from 
(SELECT TO_CHAR(COL1, 'yyyy-MM-dd') as col1 FROM TABLE1
) T
WHERE col1 ='2021-11-12'