How to Select Weekdays Only in Oracle SQL

September 9, 2024 By Matthew Rathbone

Identifying Weekdays Using Oracle’s `TO_CHAR()` Function

The following example shows how to use TO_CHAR() to return only rows where the date falls on a weekday.

SELECT order_id, order_date
FROM orders
WHERE TO_CHAR(order_date, 'DY') NOT IN ('SAT', 'SUN');

Expected Output:

ORDER_ID | ORDER_DATE
---------|------------
1001     | 04-SEP-2023
1002     | 05-SEP-2023
1005     | 06-SEP-2023
1010     | 07-SEP-2023

In this query:

TO_CHAR(order_date, 'DY') extracts the abbreviated day name from the order_date column.
The NOT IN ('SAT', 'SUN') clause filters out any rows where the day is Saturday or Sunday.

Working with Full Day Names

If your database is set to a locale where the abbreviated day names differ, or you prefer to work with full day names, you can modify the query as follows:

SELECT employee_id, work_date
FROM employee_schedule
WHERE TO_CHAR(work_date, 'DAY') NOT IN ('SATURDAY', 'SUNDAY');

Expected Output:

EMPLOYEE_ID | WORK_DATE
------------|-----------
200         | 01-SEP-2023
201         | 02-SEP-2023
202         | 03-SEP-2023

Filtering Weekdays Between Two Dates

Often, you may want to retrieve records between two dates but only for weekdays. Here’s how you can extend the logic to do that.

Example Query: Selecting Weekdays Between Two Dates

SELECT invoice_id, invoice_date
FROM invoices
WHERE invoice_date BETWEEN TO_DATE('2023-09-01', 'YYYY-MM-DD') 
                       AND TO_DATE('2023-09-10', 'YYYY-MM-DD')
AND TO_CHAR(invoice_date, 'DY') NOT IN ('SAT', 'SUN');

Expected Output:

INVOICE_ID | INVOICE_DATE
-----------|--------------
3001       | 01-SEP-2023
3005       | 04-SEP-2023
3007       | 05-SEP-2023
3010       | 06-SEP-2023

Handling Different Languages and Locales

Oracle may display day names based on your session’s NLS (National Language Support) settings. For example, if your session is set to a language other than English, the day abbreviations like ‘MON’, ‘TUE’ may be different. You can check the current settings by running:

SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_TERRITORY';

If necessary, you can override these settings at the session level to ensure consistent results:

ALTER SESSION SET NLS_TERRITORY = 'AMERICA';

This sets the territory to ‘AMERICA’, ensuring that day names like ‘MON’, ‘TUE’, etc., are used.

Using the `NEXT_DAY()` Function

Sometimes you may want to calculate the next weekday from a given date. Oracle’s NEXT_DAY() function helps you find the next occurrence of a specific weekday.

Example Query: Finding the Next Weekday

SELECT NEXT_DAY(SYSDATE, 'MONDAY') AS next_monday
FROM dual;

Expected Output:

NEXT_MONDAY
------------
11-SEP-2023

In this case, NEXT_DAY(SYSDATE, 'MONDAY') returns the next ‘Monday’ after the current date.

Conclusion

Filtering weekdays in Oracle SQL is a common requirement in many applications. By using the TO_CHAR() function to extract the day of the week and filtering out weekends, you can work with weekdays only. In more complex scenarios, functions like NEXT_DAY() and date ranges can be combined to create more sophisticated queries.

Other articles you may enjoy: