change DD-MMM-YYYY to YYYYMMDD in oracle

Question

i have a date 07-OCT-2017, I have to get it converted as 20171007 in oracle.

Below code doesn't work for me
select To_date(to_char('07-OCT-2017','DD-MMM-YYYY'), 'yyyymmdd') from dual;

Answer 1

Too much sugar for your dime.

  select to_char( to_date( '07-JUL-2017',
                           'DD-MON-YYYY',
                           'NLS_DATE_LANGUAGE = American'),
                 'YYYYMMDD') from dual;

Yields

TO_CHAR(
--------
20170707

Fix the details. Please edit your question to match the data you present. Still, hope this helps.

Answer 2

i have a date 07-OCT-2017, I have to get it converted as 20171007 in oracle.

Dates (stored in tables) are represented by 7 bytes - they do not have any format associated with them. If they are formatted as a string then that is the client program which you are using to access the database applying its own formatting to the date (which you can usually set via the preferences in that program).

So, you can get your date using a date literal:

SELECT DATE '2017-10-07' FROM DUAL;

or, using TO_DATE:

SELECT TO_DATE( '07-OCT'2017', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=English' )
FROM   DUAL;

However, the client program you are using will convert those 7-bytes to something that you, the user, can understand using its default format for dates. You can change this default format in:

• SQL Developer using this answer.
• SQL/Plus using this this answer

If you do not want to change your defaults or need to show it in a format for other users then you will need to convert your date (internally 7-bytes without a format) to a formatted string using TO_CHAR:

SELECT TO_CHAR( DATE '2017-10-07', 'YYYYMMDD' ) FROM DUAL;

or

SELECT TO_CHAR(
         TO_DATE( '07-OCT'2017', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=English' ),
         'YYYYMMDD'
       )
FROM   DUAL;