With Oracle Database, we can use the TO_CHAR(datetime) function to return a datetime value, formatted in a way that we specify.

We can use this function to return the day name from a date (as well as any other parts of the datetime value).

Full Day Name

When it comes to returning the day name from a date, we have the option of getting the full day name or its abbreviated version.

To get the full day name, the DAY format element does the trick:

SELECT TO_CHAR(DATE '2037-10-03', 'DAY')
FROM DUAL;

Result:

SATURDAY

Short Day Name

To get the abbreviated day name, use DY:

SELECT TO_CHAR(DATE '2037-10-03', 'DY')
FROM DUAL;

Result:

SAT

Capitalisation

We can use capitalisation if we wish. This determines the capitalisation of the output:

SELECT 
    TO_CHAR(DATE '2037-10-03', 'Day') AS "Day",
    TO_CHAR(DATE '2037-10-03', 'day') AS "day",
    TO_CHAR(DATE '2037-10-03', 'Dy') AS "Dy",
    TO_CHAR(DATE '2037-10-03', 'dy') AS "dy"
FROM DUAL;

Result:

         Day          day     Dy     dy 
____________ ____________ ______ ______ 
Saturday     saturday     Sat    sat    

Language

The language of the day name is determined either explicitly with the NLS_DATE_LANGUAGE initialisation parameter or implicitly with the NLS_LANGUAGE initialisation parameter. 

We can explicitly set the value of the NLS_LANGUAGE parameter with the ALTER SESSION statement. When we do this, it also implicitly sets the value of the NLS_DATE_LANGUAGE parameter.

However, setting the NLS_DATE_LANGUAGE parameter does not change the value of the NLS_LANGUAGE parameter. This enables us to specify a different language for format elements that return spelled values, if required.

Here’s an example of setting the NLS_DATE_LANGUAGE parameter to a different language:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'GERMAN';

Result:

Session altered.

Now, when we return the day name from a date, it’s returned in the language we just specified:

SELECT 
    TO_CHAR(DATE '2037-10-03', 'DAY') AS "Full Day Name",
    TO_CHAR(DATE '2037-10-03', 'DY') AS "Short Day Name"
FROM DUAL;

Result:

   Full Day Name    Short Day Name 
________________ _________________ 
SAMSTAG          SA               

The default language can be overridden at the function level with a third parameter that specifies the language:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'GERMAN';
SELECT 
    TO_CHAR(
        DATE '2037-10-03', 
        'Day', 
        'NLS_DATE_LANGUAGE = Spanish' 
    )
FROM DUAL;

Result:

Sábado

There are many more format elements available for formatting datetime values in Oracle. See List of Datetime Format Elements in Oracle for a full list.