Home » SQL & PL/SQL » SQL & PL/SQL » How many "SUNDAY" in a Month (oracle 10g)
How many "SUNDAY" in a Month [message #380691] Tue, 13 January 2009 00:52 Go to next message
rhnilu
Messages: 12
Registered: January 2009
Location: Bangladesh
Junior Member
No Message Body
Re: How many "SUNDAY" in a Month [message #380692 is a reply to message #380691] Tue, 13 January 2009 00:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
depends on the month
Mostly four, sometimes five [message #380694 is a reply to message #380691] Tue, 13 January 2009 00:58 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
By the way, it is strange to ask a question in title.
Re: Mostly four, sometimes five [message #380710 is a reply to message #380694] Tue, 13 January 2009 01:36 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
In the following code You find all to solve the problem:
1. a row generator do build up a list of data for a month (here actual month)
and
2. the function to get the names for the (english) days
So You have to count the wanted days and adapt it to Your problem
Good luck
SELECT (LEVEL - 1) day_nr,(LEVEL + TRUNC(SYSDATE, 'MONTH') - 1) day_date,
        TO_CHAR(LEVEL + TRUNC(SYSDATE, 'MONTH') - 1, 'DAY','NLS_DATE_LANGUAGE=ENGLISH') AS day_name
        FROM DUAL
        CONNECT BY LEVEL <=  ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1) - TRUNC(SYSDATE, 'MONTH')
  ORDER BY day_nr

DAY_NR	DAY_DATE	DAY_NAME

0	01.01.2009	THURSDAY 
1	02.01.2009	FRIDAY   
2	03.01.2009	SATURDAY 
3	04.01.2009	SUNDAY   
4	05.01.2009	MONDAY   
5	06.01.2009	TUESDAY  
6	07.01.2009	WEDNESDAY
7	08.01.2009	THURSDAY 
8	09.01.2009	FRIDAY   
9	10.01.2009	SATURDAY 
10	11.01.2009	SUNDAY   
11	12.01.2009	MONDAY   
12	13.01.2009	TUESDAY  
13	14.01.2009	WEDNESDAY
14	15.01.2009	THURSDAY 
15	16.01.2009	FRIDAY   
16	17.01.2009	SATURDAY 
17	18.01.2009	SUNDAY   
18	19.01.2009	MONDAY   
19	20.01.2009	TUESDAY  
20	21.01.2009	WEDNESDAY
21	22.01.2009	THURSDAY 
22	23.01.2009	FRIDAY   
23	24.01.2009	SATURDAY 
24	25.01.2009	SUNDAY   
25	26.01.2009	MONDAY   
26	27.01.2009	TUESDAY  
27	28.01.2009	WEDNESDAY
28	29.01.2009	THURSDAY 
29	30.01.2009	FRIDAY   
30	31.01.2009	SATURDAY 



Re: Mostly four, sometimes five [message #380713 is a reply to message #380710] Tue, 13 January 2009 01:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
_jum wrote on Tue, 13 January 2009 08:36

        CONNECT BY LEVEL <=  ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1) - TRUNC(SYSDATE, 'MONTH')



Another way to get the number of days in a month:
SQL> select to_number(to_char(last_day(sysdate), 'DD')) from dual;

TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE),'DD'))
------------------------------------------
                                        31
Re: How many "SUNDAY" in a Month [message #380723 is a reply to message #380691] Tue, 13 January 2009 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And if you don't want to use a brute force computing, you can think that the number of Sunday (or any other one) in a month is 4 or 5.
For Sunday, it is 5 only if first day is Sunday, Saturday or Friday and number of days in month is greater than or equal, respectively, 29, 30 or 31.
I let you write this in SQL.

Regards
Michel
Re: How many "SUNDAY" in a Month [message #380817 is a reply to message #380723] Tue, 13 January 2009 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> break on year skip 1
SQL> with 
  2    months as (
  3      select add_months(trunc(sysdate,'YEAR'),level-13) month
  4      from dual
  5      connect by level <= 36
  6    )
  7  select to_char(month,'YYYY') year,
  8         to_char(month,'Month') month,
  9         to_char(month,'Day') first_day,
 10         to_char(last_day(month),'Day DD') last_day,
 11         4+
 12         case 
 13           when to_char(last_day(month),'DD')
 14                - decode(to_char(month,'D'),1,0,8-to_char(month,'D'))
 15                >= 29
 16             then 1
 17           else 0
 18         end nb_sunday
 19  from months
 20  /
YEAR MONTH     FIRST_DAY LAST_DAY      NB_SUNDAY
---- --------- --------- ------------ ----------
2008 January   Tuesday   Thursday  31          4
     February  Friday    Friday    29          4
     March     Saturday  Monday    31          5
     April     Tuesday   Wednesday 30          4
     May       Thursday  Saturday  31          4
     June      Sunday    Monday    30          5
     July      Tuesday   Thursday  31          4
     August    Friday    Sunday    31          5
     September Monday    Tuesday   30          4
     October   Wednesday Friday    31          4
     November  Saturday  Sunday    30          5
     December  Monday    Wednesday 31          4

2009 January   Thursday  Saturday  31          4
     February  Sunday    Saturday  28          4
     March     Sunday    Tuesday   31          5
     April     Wednesday Thursday  30          4
     May       Friday    Sunday    31          5
     June      Monday    Tuesday   30          4
     July      Wednesday Friday    31          4
     August    Saturday  Monday    31          5
     September Tuesday   Wednesday 30          4
     October   Thursday  Saturday  31          4
     November  Sunday    Monday    30          5
     December  Tuesday   Thursday  31          4

2010 January   Friday    Sunday    31          5
     February  Monday    Sunday    28          4
     March     Monday    Wednesday 31          4
     April     Thursday  Friday    30          4
     May       Saturday  Monday    31          5
     June      Tuesday   Wednesday 30          4
     July      Thursday  Saturday  31          4
     August    Sunday    Tuesday   31          5
     September Wednesday Thursday  30          4
     October   Friday    Sunday    31          5
     November  Monday    Tuesday   30          4
     December  Wednesday Friday    31          4

Regards
Michel
Re: How many "SUNDAY" in a Month [message #380826 is a reply to message #380817] Tue, 13 January 2009 08:42 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Michael,

It's worth considering this as well.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch3globenv.htm#i1007222

Regards

Raj
Re: How many "SUNDAY" in a Month [message #380831 is a reply to message #380826] Tue, 13 January 2009 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, I know, this is a mined query, I let it as it to see if someone will see the limits of the query.
And yes, it only works for territories that have Sunday as first day of the week (which is not the case of France Wink ).
Now I let OP generalizes the query to any territory.
By the way, his country has the advantage to be the easiest case with a week starting on Friday.

Regards
Michel
Re: How many "SUNDAY" in a Month [message #380833 is a reply to message #380817] Tue, 13 January 2009 08:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Tue, 13 January 2009 15:05
 13           when to_char(last_day(month),'DD')
 14                - decode(to_char(month,'D'),1,0,8-to_char(month,'D'))
 15                >= 29

Regards
Michel


Tssk tsk tsk....
You can't subtract strings..
Wink

[Updated on: Tue, 13 January 2009 08:59]

Report message to a moderator

Re: How many "SUNDAY" in a Month [message #380834 is a reply to message #380833] Tue, 13 January 2009 09:01 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, but with TO_NUMBER it exceeds 80 characters, so I remove them. Laughing

Regards
Michel
Previous Topic: extracting multiple Numbers from String Column..
Next Topic: understanding types
Goto Forum:
  


Current Time: Fri Dec 09 19:51:22 CST 2016

Total time taken to generate the page: 0.21074 seconds