如何在SQL中排除周末？ [英] How do I exclude weekends in SQL?

问题描述

我在 Orders 表中有一个日期列 SLA_Date 。

I have a date column SLA_Date in the Orders table.

我的 SLA_Date 应该排除周末（星期六和星期日）。

My SLA_Date should exclude weekends (Saturday & Sunday). Data for weekdays should be shown alone.

如何在SQL中执行此操作？

How do I do that in SQL?

推荐答案

您只需要添加以下过滤器：

WHERE TO_CHAR(date_column, 'DY','NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN')

您的查询将如下所示：

SELECT SLA_Date
   FROM orders
 WHERE TO_CHAR(SLA_Date, 'DY','NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN')

（WITH子句仅用于构建测试用例），以下查询仅显示工作日（即，不包括星期六和星期日），范围为 2015年5月1日至 2015年5月31日：

For example(the WITH clause is only to build a test case), the below query is to display only the weekdays(i.e. excluding the Sat and Sun) ranging from 1st May 2015 to 31st May 2015:

SQL> WITH DATA AS
  2    (SELECT to_date('05/01/2015', 'MM/DD/YYYY') date1,
  3      to_date('05/31/2015', 'MM/DD/YYYY') date2
  4    FROM dual
  5    )
  6  SELECT date1+LEVEL-1 the_date,
  7        TO_CHAR(date1+LEVEL-1, 'DY','NLS_DATE_LANGUAGE=AMERICAN') day
  8  FROM DATA
  9  WHERE TO_CHAR(date1+LEVEL-1, 'DY','NLS_DATE_LANGUAGE=AMERICAN')
 10        NOT IN ('SAT', 'SUN')
 11  CONNECT BY LEVEL <= date2-date1+1;

THE_DATE  DAY
--------- ---
01-MAY-15 FRI
04-MAY-15 MON
05-MAY-15 TUE
06-MAY-15 WED
07-MAY-15 THU
08-MAY-15 FRI
11-MAY-15 MON
12-MAY-15 TUE
13-MAY-15 WED
14-MAY-15 THU
15-MAY-15 FRI
18-MAY-15 MON
19-MAY-15 TUE
20-MAY-15 WED
21-MAY-15 THU
22-MAY-15 FRI
25-MAY-15 MON
26-MAY-15 TUE
27-MAY-15 WED
28-MAY-15 THU
29-MAY-15 FRI

21 rows selected.

SQL>

有关详细示例，请参见生成日期，月份名称，周号，两个日期之间的天数

For a detailed example, see Generate Date, Month Name, Week Number, Day number between two dates

这篇关于如何在SQL中排除周末？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！