When getting the day and/or month name from a date in Oracle, you may want to return it in uppercase, lowercase, or title case.

Fortunately, this is easy to do. The result reflects the capitalisation of your format model.

Example

When using TO_CHAR() to return date parts from a date value, you use one or more format elements to produce a format model. This format model determines how the date is formatted when it’s returned.

We can use the DAY and MONTH format elements to return the day name and month name respectively.

But importantly, the capitalisation we use for these format elements determine the capitalisation of the result.

Example:

SELECT 
    TO_CHAR( DATE '2037-12-03', 'month' ) AS "month",
    TO_CHAR( DATE '2037-12-03', 'Month' ) AS "Month",
    TO_CHAR( DATE '2037-12-03', 'MONTH' ) AS "MONTH"
FROM DUAL;

Result:

       month        Month        MONTH 
____________ ____________ ____________ 
december     December     DECEMBER    

It’s the same for the DAY format element:

SELECT 
    TO_CHAR( DATE '2037-12-03', 'day' ) AS "day",
    TO_CHAR( DATE '2037-12-03', 'Day' ) AS "Day",
    TO_CHAR( DATE '2037-12-03', 'DAY' ) AS "DAY"
FROM DUAL;

Result:

         day          Day          DAY 
____________ ____________ ____________ 
thursday     Thursday     THURSDAY     

It also applies when returning the abbreviated version of the day or month.

Example:

SELECT 
    TO_CHAR( DATE '2037-12-03', 'mon' ) AS "mon",
    TO_CHAR( DATE '2037-12-03', 'Mon' ) AS "Mon",
    TO_CHAR( DATE '2037-12-03', 'MON' ) AS "MON"
FROM DUAL;

Result:

   mon    Mon    MON 
______ ______ ______ 
dec    Dec    DEC   

And:

SELECT 
    TO_CHAR( DATE '2037-12-03', 'dy' ) AS "dy",
    TO_CHAR( DATE '2037-12-03', 'Dy' ) AS "Dy",
    TO_CHAR( DATE '2037-12-03', 'DY' ) AS "DY"
FROM DUAL;

Result:

    dy     Dy     DY 
______ ______ ______ 
thu    Thu    THU   

The Year

Perhaps a lesser known fact, is that Oracle Database also provides format elements that return the full year spelt out. Specifically, YEAR and SYEAR (to cater or pre BC dates).

The capitalisation of these format elements also affects the output:

SELECT 
    TO_CHAR( DATE '2037-12-03', 'year' ) AS "year",
    TO_CHAR( DATE '2037-12-03', 'Year' ) AS "Year",
    TO_CHAR( DATE '2037-12-03', 'YEAR' ) AS "YEAR"
FROM DUAL;

Result:

                  year                   Year                   YEAR 
______________________ ______________________ ______________________ 
twenty thirty-seven    Twenty Thirty-Seven    TWENTY THIRTY-SEVEN    

And:

SELECT 
    TO_CHAR( DATE '-2037-12-03', 'syear' ) AS "syear",
    TO_CHAR( DATE '-2037-12-03', 'Syear' ) AS "Syear",
    TO_CHAR( DATE '-2037-12-03', 'SYEAR' ) AS "SYEAR"
FROM DUAL;

Result:

                  syear                   Syear                   SYEAR 
_______________________ _______________________ _______________________ 
-twenty thirty-seven    -Twenty Thirty-Seven    -TWENTY THIRTY-SEVEN   

When applying title case with the Syear format element, we need to remember that only the first character – the S character – is in uppercase, and all remaining characters are lower case (including the Y character):