Back to cookbooks list Articles Cookbook

How to Get Day Names in Oracle

  • TO_CHAR()
  • DATE
  • NLS_DATE_LANGUAGE

Problem:

You want to extract the name of the day of the week from a date in Oracle.

Download SQL for Data Analysis Cheat Sheet

Solution 1:

To extract the day name from the date, use the TO_CHAR() function. The first parameter is the date and the second is the format in which we want to output the date. To extract the day name, the format should be 'Day':

SELECT 
  TO_CHAR(DATE '2022-01-01', 'Day') 
FROM DUAL;

The result is Saturday.

Discussion:

This is not the only format you can use to extract day names. Here are several alternatives:

  • 'DAY' would return SATURDAY
  • 'day' would return saturday
  • 'DY' would return SAT
  • 'Dy' would return Sat
  • 'dy' would return sat
  • 'D' would return 7 (based on the range 1-7, from Sunday to Saturday)

You can also extract the day name in another language. To do this, you need to use the third parameter, NLS_DATE_LANGUAGE. This parameter can take any valid language name; you can find more on this in NLS_DATE_LANGUAGE’s entry in the Oracle documentation. Let’s try an example.

Solution 2:

Let’s extract the day name in German.

SELECT 
  TO_CHAR(DATE '2022-01-01', 'Day', 'NLS_DATE_LANGUAGE = German') 
FROM DUAL;

The result is Samstag.

Recommended courses:

Recommended articles:

See also: