Oracle SQL 中排除周末和节假日的终极指南（2024 版）

Hey there! It’s your Holiday Little Assistant back with another techie holiday hack. Recently one of our SQL-savvy users asked me how to whip up queries that ignore weekends and public holidays in Oracle – you know, for those “business days only” reports that make finance teams happy. Let me break it down for you in plain English with some real-world examples.

First things first – calculating business days is way more common than you’d think. Whether you’re tracking SLA timelines, processing periods, or just counting working days between dates, bypassing Saturdays, Sundays, and holidays is a frequent headache. The good news? Oracle SQL’s got some slick date functions to handle this, but there’s a catch…

周末问题（以及简单的解决方法）

Weekends are the easy part. Oracle’s TO_CHAR function with the ‘D’ format model lets you check day numbers (1=Sunday through 7=Saturday). Here’s a quick trick to filter them out:


选择订单日期
来自订单
WHERE TO_CHAR(order_date，'D') NOT IN ('1'，'7')


But wait – this assumes Sunday is day 1. Some locales start the week on Monday, so double-check with 从双重中选择 TO_CHAR(SYSDATE，'D') 首先。专业提示：为了获得更清晰的代码，请使用 不在（6,7） if you’re ISO-standard (Monday=1).

假期头痛（以及如何解决）

Here’s where things get spicy. Unlike weekends, holidays aren’t baked into Oracle – they change annually and vary by country/region. You’ve got two solid approaches:

1. 假期表方法：创建一个包含所有相关日期的 HOLIDAYS 表，然后：

选择工作日期
来自项目日期
其中 TO_CHAR(work_date，'D') 不在 (6,7)
并且工作日期不在（从假期中选择假期日期）


2. 动态日期计算：对于固定假日（例如美国的 7 月 4 日），使用条件逻辑：

哪里不 (
（TO_CHAR（date_field，'MMDD'）='0101'——新年
或 (TO_CHAR(date_field, 'MMDD') = '1225' -- 圣诞节
-- 根据需要添加更多固定日期
)


专业级工作日计算

Need to count business days between dates? Combine our tricks with a recursive CTE or calendar table. Here’s a slick example using a generated date range:


使用日期范围作为 (
选择开始日期 + 级别 - 1 作为计算日期
从（选择 TO_DATE（'01-JAN-2024'）作为开始日期，
TO_DATE('31-DEC-2024') AS 结束日期 FROM Dual)
按级别连接 <= 结束日期 - 开始日期 + 1
)
选择 COUNT(*) 作为工作日
从日期范围开始
其中 TO_CHAR(calc_date，'D') 不在 (6,7)
并且计算日期不在（从公司假期中选择假期日期）


Remember to handle your specific holiday schedule – federal holidays differ from corporate holidays, and international operations add more complexity. Some teams maintain separate tables for observed vs. actual holidays too!

So there you have it – your complete toolkit for weekend and holiday exclusions in Oracle SQL. Whether you’re building reports, calculating deadlines, or analyzing business cycles, these techniques will keep your date math accurate. And hey, if your company suddenly declares National Pizza Day as a holiday (we wish!), just add it to your holidays table and your queries will automatically respect the new day off.

FAQpro tip: Always test edge cases around holiday weekends – that Tuesday after a Monday holiday can sometimes trip up date calculations. Thanks for reading, and may all your queries return quickly (unlike government offices on federal holidays)! Got a tricky date scenario? Reach out and we’ll help crack it.