Home » SQL & PL/SQL » SQL & PL/SQL » Chk the days
icon2.gif  Chk the days [message #202569] Fri, 10 November 2006 07:23 Go to next message
chmlaeeque
Messages: 59
Registered: September 2006
Member
hay can i count that how many sundays are in the current month
Re: Chk the days [message #202575 is a reply to message #202569] Fri, 10 November 2006 07:35 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, you can. At least, I believe you can.

Take a calendar, open the current month page and count red-coloured numbers (they usually represent Sundays).

./fa/1587/0/

Just kidding ... you would probably like Oracle to do it for you, right? Perhaps you could use something like this:
SELECT COUNT(*) number_of_sundays
FROM (SELECT ROWNUM rn
        FROM ALL_OBJECTS
       WHERE ROWNUM <= LAST_DAY(SYSDATE) - TRUNC(SYSDATE, 'mm')
     )
WHERE TO_CHAR(TRUNC(SYSDATE, 'mm') + rn - 1, 'DY') = 'SUN';
Re: Chk the days [message #202577 is a reply to message #202569] Fri, 10 November 2006 07:39 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Yes you can count the number of Sundays in a given month:
select count(1) days
from ( select to_date('012006','mmyyyy')+level-1 theday
       from dual
       connect by level <=    add_months(to_date('012006','mmyyyy'),1)
                            - to_date('012006','mmyyyy')
     ) v
where to_char(theday,'Dy') = 'Sun'
/


For the current month that would be:
SQL> ed
Wrote file afiedt.buf

  1  select count(1) days
  2  from ( select trunc(sysdate,'MM')+level-1 theday
  3         from dual
  4         connect by level <=    add_months(trunc(sysdate,'MM'),1)
  5                              - trunc(sysdate,'MM')
  6       ) v
  7* where to_char(theday,'Dy') = 'Sun'
SQL> /

      DAYS
----------
         4

MHE
Previous Topic: If you have used QP_Price_List_PUB.Process_Price_List plz send me sample code
Next Topic: build sql
Goto Forum:
  


Current Time: Sat Dec 10 18:46:07 CST 2016

Total time taken to generate the page: 0.20899 seconds