Home » SQL & PL/SQL » SQL & PL/SQL » want to get number of sundays in a month
icon4.gif  want to get number of sundays in a month [message #197346] Wed, 11 October 2006 00:46 Go to next message
cmuraz
Messages: 1
Registered: October 2006
Location: India
Junior Member

i need to get the number of sundays in a month provided with only a particular date...
Re: want to get number of sundays in a month [message #197353 is a reply to message #197346] Wed, 11 October 2006 01:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Feels like homework time again.

Still, it helps to get my brain warmed up in a morning, so here's two completely different solutions.
One works out the number of sundays based on the number of days in the month and the day the month starts on, and the other uses a row generator to step through the month one day at a time, counting the sundays.

Note that both of these are NLS specific. Some NLS regions have Sunday as day 1 of the week, not day 7.

select floor((last_day(trunc(dte)) - trunc(dte,'MM'))/7) --  1 per complete week
      ,case when to_char(trunc(dte,'MM'),'D') + mod(last_day(trunc(dte)) - trunc(dte,'MM'),7) >= 7 then 1
            else 0 
            end
from (select to_date('07-07-2006','dd-mm-yyyy') dte from dual);

select sum(decode(to_char(dte,'D'),7,1,0))
from  (select trunc(to_date('07-10-2006','dd-mm-yyyy'),'MM')+level -1 dte
       from   dual
       connect by level <= to_char(last_day(to_date('07-10-2006','dd-mm-yyyy')),'dd'));
Re: want to get number of sundays in a month [message #197355 is a reply to message #197346] Wed, 11 October 2006 01:05 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If you search the board you should be able to find a solution but it has been quite a while since it was posted so, here's one:
SQL> select count(thedate) x
  2  from   ( select to_date('200605','YYYYMM')+level-1 thedate
  3           from   dual
  4           connect by level <   add_months(to_date('200605','YYYYMM'),1)
  5                              - to_date('200605','YYYYMM')
  6         )
  7  where  to_char(thedate,'DY','NLS_DATE_LANGUAGE=AMERICAN') = 'SUN'
  8  /

         X
----------
         4
This shows the number of Sundays in May 2006. The third parameter in the to_char ('NLS_DATE_LANGUAGE') is to make sure Oracle takes the American (English?) abbreviation of the day of the week ("SUN") and is optional (and rarely used).

MHE

[Updated on: Wed, 11 October 2006 01:06]

Report message to a moderator

Re: want to get number of sundays in a month [message #197410 is a reply to message #197355] Wed, 11 October 2006 03:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I didn't even know that parameter was there!
Learning Experience - check.
Re: want to get number of sundays in a month [message #197420 is a reply to message #197410] Wed, 11 October 2006 03:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
We use it sometimes because some of our databases are set to Dutch. This is fun when you are working with decimal points (comma in Dutch).

MHE
Re: want to get number of sundays in a month [message #197422 is a reply to message #197346] Wed, 11 October 2006 04:08 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Here is another NLS independent solution:

select to_char(sysdate,'FMMONTH YYYY') MONTH,
	ceil(to_char(trunc(last_day(sysdate)+1,'IW')-1,'DD')/7) SUNDAYS from dual;

MONTH	     SUNDAYS
------------ -------
OCTOBER 2006       5

Previous Topic: table data into a text file
Next Topic: query for output in group
Goto Forum:
  


Current Time: Fri Dec 09 11:24:19 CST 2016

Total time taken to generate the page: 0.08992 seconds