sqloraclecastingansi-sqlto-char

Formatting date when using CAST

I would like to use CAST to convert a DATE type to a VARCHAR2 type.

DBUSER >SELECT CAST(CURRENT_DATE AS VARCHAR2(20)) THE_DATE from DUAL;
THE_DATE
--------------------
09-AUG-17

However, I need the VARCHAR2 result to be formatted as 'YYYYMM'. I know that I can achieve this effect by changing the session date format, but I would rather not do that.

DBUSER >ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMM';
Session altered.

DBUSER >SELECT CAST(CURRENT_DATE AS VARCHAR2(20)) THE_DATE from DUAL;
THE_DATE
--------------------
201708

I would like to avoid using Oracle's proprietary TO_CHAR() function. Does anyone have a suggestion on how to do that?

Solution

  • This may be help you:

    SELECT extract(year from CURRENT_DATE) || case when extract(month from CURRENT_DATE) <10 THEN '0' || extract(month from CURRENT_DATE) END  THE_DATE from DUAL;