CODEWITHSUNDEEP
sqloracle-databasedate
Shubhangi
Shubhangi

Reputation: 13

Oracle date format:varchar to date then char and then date

I have a varchar value '20-OCT-2019' I want to load the same in date column with the format '10/20/2019'.

I have tried below but it is not giving expected output.

select to_date(to_char(to_date('20-OCT-2019','DD-MON-YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') 
  from dual

Upvotes: 1

Views: 592

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

In Oracle, DATE values do not have a particular format such as DD-MON-YYYY OR DD/MM/RR or etc. All DATE fields contain sub-fields for the century, year, month, day, hours, minutes, and seconds, meaning that all DATE fields in Oracle are really timestamp values with a precision of one second. Oracle will by default convert a DATE to a character string using the DD-MON-YY format, but this is just default format - it does not mean that the date is stored in DD-MON-RR format.

Upvotes: 5

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You cannot load your string data in a string column. So the outermost to_date conversion has no sense, but if you want to display in the desired format you need such a conversion below :

select  to_char(
                to_date('20-OCT-2019','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
                   ,'MM/DD/YYYY')
    as "Appearance in New Format"                   
  from dual;

    Appearance in New Format
            10/20/2019

without forgetting to use NLS_DATE_LANGUAGE option against your different local language settings being other than English(In this case OCT: October is an abbreviation in English Language)

P.S. actually you don't save a date column in a certain format as @Bob Jarvis pointed out but they're shown as depending on your nls_date_format settings such as setting with this sample :

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Upvotes: 1

Related Questions