CODEWITHSUNDEEP
sqloracle-databasedate
ramakri
ramakri

Reputation: 201

Date conversion in Oracle not working as expected

ORACLE 10 g db: convert date format from 

 02-JUL-14 
    to 
 02/JUL/14

I tried using the the select query below to get "02/JUL/14" instead it shows "02-JUL-14":

SELECT ROUTINGNUM , to_date (EFFDATE,'DD/MM/YYYY')  FROM hat;

Can anybody please help reading this.

Upvotes: 1

Views: 1109

Answers (3)

mrjoltcola
mrjoltcola

Reputation: 20862

If your EFFDATE field is already a date type, then you just need to change NLS_DATE_FORMAT.

alter session set NLS_DATE_FORMAT = 'DD/MM/YYYY';
select eff_date from hat;

Or use TO_CHAR on EFFDATE

select to_char(eff_date, 'DD/MM/YYYY') from hat;

If you typically deal with a certain format of date, then you can change the default for your instance by setting one or more of the NLS init parameters.

sqlplus / as sysdba
show parameter nls

Upvotes: 0

Bananan
Bananan

Reputation: 609

TO_DATE function is used for converting from String to Date type. Use TO_CHAR function instead.

Upvotes: 0

Randy
Randy

Reputation: 16673

if EFFDATE is a date column,

SELECT ROUTINGNUM , to_char( EFFDATE,'DD/MON/YYYY')  FROM hat;

if it is a String in the format DD-MON-YY

SELECT ROUTINGNUM , to_char( to_date( EFFDATE,'DD-MON-YY') ,'DD/MON/YYYY')  
FROM hat;

Upvotes: 2

Related Questions