Oracle's SQL how to convert the date's format

user_12166537 0
Viewed 85

this is my table:

table name EXAMPLE column name DATE

this is the output for the following query:

SELECT date 
  FROM example;

1/23/2010

I want to convert the output to:

23-Jan-10

Is it possible?

Thanks

type-conversion oracle sql
3 Answers

You can use formatting along with TO_CHAR() conversion such as

SELECT TO_CHAR(dt,'DD-Mon-RR') 
  FROM example

where

  • DATE is not a good name for a column as being a reserved keyword. So, I've replaced it with dt
user_57028110

If your column is in varchar/text.. then you can first convert to date then back to char

SELECT 
TO_CHAR( 
to_date('1/23/2010','mm/dd/yyyy'),'DD-Mon-YY'
) 
datec FROM dual;

If it is in date format, to_char only will do

SELECT 
TO_CHAR(date_column, 'DD-Mon-YY' ) 
datec FROM dual;
user_57376540

A DATE data type is a binary format that is stored in 7-bytes that has no format and always contains the components: year (stored as century and year-of-century), month, day, hour, minute and second.

Therefore, you cannot change the format of a DATE data type.

If instead, you ask the question:

How can format a DATE to output it as 23-Jan-10?

Then you can convert the DATE to a formatted string using the TO_CHAR function:

SELECT TO_CHAR(your_date_column, 'DD-MON-YY', 'NLS_DATE_LANGUAGE=English')
         AS formatted_date
FROM   your_table;

Alternatively, if you want the output as a DATE data type then you can use:

SELECT your_date_column
FROM   your_table;

and change the user interface (SQL/Plus, SQL Developer, Toad, PLSQLDeveloper, PHP, Java, etc.) you are using to alter how that that user interface displays dates. The solution is going to depend on which user interface you are using but for SQL/Plus and SQL Developer, you can change Oracle's NLS_DATE_FORMAT session parameter:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY';

Other user interfaces will have different solutions specific to those interfaces.

user_15092640
Related