如何确定哪个日期是一个月中的特定星期六或星期日

Question

我能找到某一天的日期吗?这个日期是某一个月的星期六和星期日。例如，考虑一下一月至2017年。下列日期是周末

7/1/2017 - Saturday

14/1/2017 - Saturday

21/1/2017 - Saturday

28/1/2017 - Saturday 

1/1/2017 - Sunday

8/1/2017 - Sunday

15/1/2017 - Sunday

22/1/2017 - Sunday

29/1/2017 - Sunday

我希望对此进行SQL查询，这样，当我将月和年作为输入时，它应该将上述所有日期(仅为星期六和星期日的日期)作为输出。

Answer 1

尝尝这个。在第4行中，可以将年/月组合起来。例如，我使用过2017-02。

SELECT TRUNC(dt, 'MM') + LEVEL - 1 AS mdate
,to_char(TRUNC(dt, 'MM') + LEVEL - 1,'DY' ) as m_day
FROM 
(select to_date('2017-02','YYYY-MM') as dt from dual) --this is where you can put year/month
where to_char(TRUNC(dt, 'MM') + LEVEL - 1,'DY' ) in ('SAT','SUN')
CONNECT BY TRUNC(TRUNC(dt, 'MM') + LEVEL - 1, 'MM') = TRUNC(dt, 'MM')

输出

MDATE       M_DAY 
04-FEB-2017 SAT
05-FEB-2017 SUN
11-FEB-2017 SAT
12-FEB-2017 SUN
18-FEB-2017 SAT
19-FEB-2017 SUN
25-FEB-2017 SAT
26-FEB-2017 SUN

Answer 2

SQL查询中的简单调整可以为您提供所需的输出。希望这能有所帮助。

SELECT A.DT,
  TO_CHAR(A.DT,'FmDay') DAY
FROM
  (SELECT TRUNC(sysdate,'MONTH')+level DT
  FROM DUAL
    CONNECT BY level <= TO_CHAR(LAST_DAY(sysdate),'fmDD')
  )a
WHERE TO_CHAR(a.dt,'FmDAY') IN ('SATURDAY','SUNDAY');

Answer 3

我将做一些以上代码上的重大更改，我们将在一年中的所有周日和周六进行更改

SELECT TRUNC(dt, 'yyyy') + LEVEL - 1 AS mdate
,to_char(TRUNC(dt, 'yyyy') + LEVEL - 1,'DY' ) as m_day
FROM
(select to_date('2020-01','YYYY-MM') as dt from dual) --this is where you can put year/month
where to_char(TRUNC(dt, 'yyyy') + LEVEL - 1,'DY' ) in ('SAT','SUN')
CONNECT BY TRUNC(TRUNC(dt, 'yyyy') + LEVEL - 1, 'yyyy') = TRUNC(dt, 'yyyy')