How to find day of the week on Oracle

 In Oracle SQL, you can find the day of the week for a date using the built-in functions like TO_CHAR, NEXT_DAY, or others. Here's how:

---

1. Using TO_CHAR Function

The TO_CHAR function can format a date to display the day of the week.

Example:

SELECT TO_CHAR(SYSDATE, 'DAY') AS day_of_week FROM dual;

• Output:
  Returns the full day name (e.g., THURSDAY).

Key Format Models:

• DAY: Full name of the day, padded to 9 characters.
• DY: Abbreviated name (e.g., THU).
• D: Numeric representation (1 for Sunday, 2 for Monday, etc., depending on the NLS_TERRITORY setting).

---

2. Numeric Day of the Week

To get the numeric day (1-7) based on NLS_TERRITORY:

SELECT TO_CHAR(SYSDATE, 'D') AS day_number FROM dual;

---

3. Getting Specific Days Using NEXT_DAY

The NEXT_DAY function returns the next occurrence of a specific day.

Example:

SELECT NEXT_DAY(SYSDATE, 'SUNDAY') AS next_sunday FROM dual;

---

4. Customized Day Formats

You can use TRIM to remove padding from DAY format:

SELECT TRIM(TO_CHAR(SYSDATE, 'DAY')) AS trimmed_day_of_week FROM dual;

---

5. Example with a Specific Date

To find the day of the week for a specific date:

SELECT TO_CHAR(TO_DATE('23-JAN-2025', 'DD-MON-YYYY'), 'DAY') AS day_of_week FROM dual;

---

6. NLS Settings Impact

The output depends on the NLS_TERRITORY and NLS_DATE_LANGUAGE settings, which control the start of the week and the language of the day names.

Example:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'FRENCH';
SELECT TO_CHAR(SYSDATE, 'DAY') AS day_in_french FROM dual;

---

Practical Use in Queries

You can use these functions in WHERE clauses or to group data by day:

Example: Grouping by Day

SELECT TO_CHAR(order_date, 'DAY') AS day_of_week, COUNT(*) AS orders
FROM orders
GROUP BY TO_CHAR(order_date, 'DAY');

This is how you can efficiently work with days of the week in Oracle SQL!