Home » SQL & PL/SQL » SQL & PL/SQL » How many "SUNDAY" in a Month (oracle 10g)
How many "SUNDAY" in a Month Tue, 13 January 2009 00:52
 rhnilu Messages: 12Registered: 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
 Frank Messages: 7880Registered: 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
 flyboy Messages: 1844Registered: 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
 _jum Messages: 515Registered: 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
 Frank Messages: 7880Registered: 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
 Michel Cadot Messages: 65076Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Michel Cadot Messages: 65076Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 S.Rajaram Messages: 1027Registered: October 2006 Location: United Kingdom Senior Member
Michael,

It's worth considering this as well.

Regards

Raj
Re: How many "SUNDAY" in a Month [message #380831 is a reply to message #380826] Tue, 13 January 2009 08:54
 Michel Cadot Messages: 65076Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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 ).
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
 Frank Messages: 7880Registered: 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..

[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
 Michel Cadot Messages: 65076Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Yes, but with TO_NUMBER it exceeds 80 characters, so I remove them.

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

Current Time: Thu Jul 20 07:44:13 CDT 2017

Total time taken to generate the page: 0.12765 seconds