Home » SQL & PL/SQL » SQL & PL/SQL » how many Sundays (Oracle 9i)
how many Sundays [message #307142] Mon, 17 March 2008 23:50 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Is there any function to get how many Sundays in a month
Re: how many Sundays [message #307151 is a reply to message #307142] Tue, 18 March 2008 00:37 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
There's no built-function.But, you can easily achive this.Can you post what you tried and what is the problem you are facing?

Search :"Calendar" in this forum.


regards,
Re: how many Sundays [message #307162 is a reply to message #307142] Tue, 18 March 2008 01:04 Go to previous messageGo to next message
dev01
Messages: 13
Registered: March 2005
Junior Member
SELECT COUNT(tab1.day) AS no_of_sundays 
  FROM (SELECT    to_char(to_date('01-apr-2008')+level-1,'day')  AS day 
          FROM    dual
    CONNECT BY    level-1 < to_char(last_day(to_date('01-apr-2008')),'dd')) tab1
 WHERE  TRIM(tab1.day) = 'sunday'

Re: how many Sundays [message #307166 is a reply to message #307162] Tue, 18 March 2008 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@dev01,

1/ Your query does not work in all environment:

SQL> SELECT COUNT(tab1.day) AS no_of_sundays 
  2    FROM (SELECT    to_char(to_date('01-apr-2008')+level-1,'day')  AS day 
  3            FROM    dual
  4      CONNECT BY    level-1 < to_char(last_day(to_date('01-apr-2008')),'dd')) tab1
  5   WHERE  TRIM(tab1.day) = 'sunday';
  FROM (SELECT    to_char(to_date('01-apr-2008')+level-1,'day')  AS day
                                  *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected


2/ Why using brut force (counting each day) when you know there is at most 1 Sunday per week and 7 days in a week?

Regards
Michel
Re: how many Sundays [message #307277 is a reply to message #307142] Tue, 18 March 2008 05:35 Go to previous messageGo to next message
dev01
Messages: 13
Registered: March 2005
Junior Member
Thanks Michel for pointing out my errors. Do you mean a query like this ?

SQL> alter session set nls_date_format = "dd-mon-yyyy"
  2  /

Session altered.

SQL> SELECT  COUNT(next_day(to_date('01-feb-2008')+((level-1)*7),'SUN')) AS No_of_sundays
  2  FROM    dual
  3  WHERE   next_day(to_date('01-feb-2008')+((level-1)*7),'SUN') <= last_day(to_date('01-feb-2008'))
  4  CONNECT BY  level <= 7
  5  /

NO_OF_SUNDAYS
-------------
            4
Re: how many Sundays [message #307282 is a reply to message #307277] Tue, 18 March 2008 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> alter session set nls_date_format = "dd-mon-yyyy"
  2  /

Session altered.

SQL> SELECT  COUNT(next_day(to_date('01-feb-2008')+((level-1)*7),'SUN')) AS No_of_sundays
  2  FROM    dual
  3  WHERE   next_day(to_date('01-feb-2008')+((level-1)*7),'SUN') <= last_day(to_date('01-feb-2008'))
  4  CONNECT BY  level <= 7
  5  /
WHERE   next_day(to_date('01-feb-2008')+((level-1)*7),'SUN') <= last_day(to_date('01-feb-2008'))
                         *
ERROR at line 3:
ORA-01843: not a valid month

Not fully, the query must not rely on any parameter (but maybe calendar otherwise the question itself is meaningless).
In addition, this does not work if the first day of the month is a sunday.

Regards
Michel
Re: how many Sundays [message #307315 is a reply to message #307277] Tue, 18 March 2008 08:00 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Link to how to use TO_DATE

http://www.techonthenet.com/oracle/functions/to_date.php
Previous Topic: join (merged 3)
Next Topic: Needed a sql query for below scenario
Goto Forum:
  


Current Time: Wed Dec 07 05:06:20 CST 2016

Total time taken to generate the page: 0.07043 seconds