Format a date with TO_DATE 1

[Slippenos]

I am brand new to Oracle and am having a hard time with the TO_DATE function. I want to format a date that is in this notation: '2005-01-06' to this notation: '01/06/2005'.

SELECT TO_DATE(col1, 'MM/DD/YYYY') AS formattedcol
FROM table1

However, the rows returned come in the ansi format, not the format in which I specified.

Any thoughts? Thanks in advance.

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[SantaMufasa]

Slippenos, Welcome to the World of Oracle! I hope you enjoy your experiences here.

Could you please confirm the data type and description of "table1.col1"? Is "col1" a VARCHAR2 column or is it already a DATE data type column? (If you are connected to SQL*Plus as the owner of table1, and if you issue the command, "SQL> describe table1", what does it say about "col1"?)

If col1 is already a DATE data type, then you can issue this command:

SELECT TO_CHAR(col1, 'MM/DD/YYYY') AS formattedcol
FROM table1;

Otherwise, if col1 is VARCHAR2(9), then first, that is very poor form in Oracle...dates should always be in DATE data type; second, you can issue this query that results in the format you want:

SELECT to_char(TO_DATE(col1,'YYYY-MM-DD'),'MM/DD/YYYY') AS formattedcol
FROM table1;

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Slippenos]

Thank you. The move to Oracle has been pretty exciting for the other developers and I- as we have just made the move from MySQL.

As it turned out- the field in question was a varchar2, so the latter query worked fine. My DBA informed me this would change ASAP.

Thanks for the great response.


[blue]Go to work to learn. Don't go to work to earn.[/blue]