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):