Geek Answers Handbook

The number of special days of the week in a time period

I am trying to find the number of Xdays (Mondays, Fridays, etc.) after a month. I could always go through the days for a month, find their day of the week, summarize and store it, but I think there is a better way to do this.

Ideally, I would like to get the result for "how many Mondays, Mondays and Fridays (or any days) in September 2015?".

I can find how to get week days for a certain period of time. I can get this information from dates already entered in the database, but I want to know there Tuesday, even if there is no data on that day.

+4
source share
5 answers

Michiel T, trunc , :

select count(*)
from
  (select trunc(sysdate, 'MM') + (level - 1) dt
   from dual
   connect by level <= extract(day from last_day(trunc(sysdate, 'MM'))))
where to_char(dt, 'FMDAY') = 'MONDAY'
+1

, PL/SQL PIPELINED :

CREATE OR REPLACE TYPE days_tbl AS TABLE OF DATE;

CREATE OR REPLACE FUNCTION days_for_month(basedate IN DATE)
RETURN days_tbl PIPELINED
AS
    month VARCHAR(2);
    thedate DATE := TRUNC(basedate,'MON');
BEGIN
    LOOP
      PIPE ROW(thedate);
      EXIT WHEN thedate = LAST_DAY(thedate);
      thedate := thedate + 1;
    END LOOP;
END;

:

SELECT COLUMN_VALUE
  FROM TABLE(days_for_month(SYSDATE)) 
  WHERE TO_CHAR(COLUMN_VALUE,'FMDAY') = 'MONDAY';

( ), , :

SELECT COLUMN_VALUE
  FROM TABLE(days_for_month(SYSDATE)) 
  WHERE TO_CHAR(COLUMN_VALUE,'FMDAY') IN ('SUNDAY','SATURDAY');

:

" , ( ) 2015 ?".

SELECT COUNT(*)
  FROM TABLE(days_for_month(TO_DATE('09-2015','MM-YYYY'))) 
  WHERE TO_CHAR(COLUMN_VALUE,'FMDAY') IN ('MONDAY','TUESDAY','FRIDAY');

:

COUNT(*)
13

, :

SELECT T.D, COUNT(*)
  FROM TABLE(days_for_month(TO_DATE('09-2015','MM-YYYY'))),
       (SELECT 'MONDAY' AS D FROM DUAL UNION
        SELECT 'TUESDAY' FROM DUAL UNION
        SELECT 'FRIDAY' FROM DUAL) T
  WHERE TO_CHAR(COLUMN_VALUE,'FMDAY') = T.D
  GROUP BY T.D

:

D       COUNT(*)
TUESDAY 5
FRIDAY  4
MONDAY  4
+1

:

NEXT_DAY(TRUNC(DATE '2014-09-19', 'MM')-1, 'TUESDAY')

:

NEXT_DAY(LAST_DAY(DATE '2014-09-19')-7, 'TUESDAY')

, :

SELECT 1 + (NEXT_DAY(LAST_DAY(DATE '2014-09-19')-7, 'TUESDAY') - NEXT_DAY(TRUNC(DATE '2014-09-19', 'MM')-1, 'TUESDAY'))/7 AS TUESDAYS_IN_MONTH
FROM dual;
0

Oracle , , . 9 () 2015

SELECT COUNT(*)
FROM   ( SELECT RTRIM(TO_CHAR(TO_DATE(TO_CHAR(LEVEL, '09')||'09'||'2015','DDMMYYYY'),'DAY')) AS days
         FROM dual
         WHERE ROWNUM <= EXTRACT(DAY FROM LAST_DAY(TO_DATE('09'||'2015','MMYYYY')))
         CONNECT BY LEVEL=ROWNUM
       )
WHERE  days = 'MONDAY'
       ;
-1

@Leeish :

", script, , , "

( MS Sql, )

(, "" ):

SELECT DATENAME(dw,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))

:

select datediff(d,  GETDATE(), dateadd(m, 1,  GETDATE()))

, .

-2

More articles:


All Articles