Home » SQL & PL/SQL » SQL & PL/SQL » how to count number of thursday in a month
how to count number of thursday in a month [message #20624] Sun, 09 June 2002 02:11 Go to next message
yram
Messages: 75
Registered: February 2001
Member
hi
give me a query that will find the number of thursday in a month

bye
yram
Re: how to count number of thursday in a month [message #20625 is a reply to message #20624] Sun, 09 June 2002 04:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
SQL> ED
Wrote file afiedt.buf

  1  SELECT ((NEXT_DAY(LAST_DAY(TRUNC(SYSDATE))-7,'THURSDAY')  -NEXT_DAY(TRUNC(SYSDATE,'MONTH')-1,'T
  2* FROM DUAL
SQL> /

 THURSDAYS
----------
         4

[b]
And here is a more generic function..to find count of all the days. Input is start date of the month [/b]

SELECT DECODE(TO_CHAR(TO_DATE('01-JUNE-02') + ROWNUM - 1,'D'), 
1, 'SUNDAY', 
2,'MONDAY', 
3,'TUESDAY', 
4,'WEDNESADY', 
5,'THURSDAY', 
6,'FRIDAY', 
7,'SATURDAY') DAY, COUNT(1) 
FROM DBA_OBJECTS 
WHERE ROWNUM <= TO_CHAR(LAST_DAY('01-JUNE-02'),'DD') 
GROUP BY TO_CHAR(TO_DATE('01-JUNE-02') + ROWNUM - 1,'D') 
ORDER BY TO_CHAR(TO_DATE('01-JUNE-02') + ROWNUM - 1,'D') 
SQL> /

DAY         COUNT(1)
--------- ----------
SUNDAY             5
MONDAY             4
TUESDAY            4
WEDNESADY          4
THURSDAY           4
FRIDAY             4
SATURDAY           5

7 rows selected.

Re: how to count number of thursday in a month- UPDATED [message #20626 is a reply to message #20624] Sun, 09 June 2002 06:00 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
<H5> SORRY THE POSTING WAS TRUNCATED...</H1>
SQL> ed
Wrote file afiedt.buf

  1  SELECT ((NEXT_DAY(LAST_DAY(TRUNC(SYSDATE))-7,'THURSDAY')-
  2  NEXT_DAY(TRUNC(SYSDATE,'MONTH')-1,'THURSDAY')) / 7)
  3   +1 AS THURSDAYS
  4* FROM DUAL
SQL> /

 THURSDAYS
----------
         4

SQL> 
Previous Topic: imp question
Next Topic: Hierarchical List
Goto Forum:
  


Current Time: Thu Apr 25 18:31:29 CDT 2024