OraFAQ Forum: SQL & PL/SQL » How to find the number of saturdays and Sundays?

Home » SQL & PL/SQL » SQL & PL/SQL » How to find the number of saturdays and Sundays?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to find the number of saturdays and Sundays? [message #195495]

Fri, 29 September 2006 01:29

srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA

Member

Hi All,

Please let me know the query how to get the saturdays and sundays with Dates in a particular year. Say in year 2005 i have 52 saturdays... I would lkie to get the daets of all that saturdays .
Help me with the query.

Regards
Srini..

Report message to a moderator

Re: How to find the number of saturdays and Sundays? [message #195511 is a reply to message #195495]

Fri, 29 September 2006 02:23

vijayanarayanan
Messages: 11
Registered: September 2006
Location: chennai

Junior Member

TO DISPLAY THE DATE OF SATURDAYS AND SUNDAYS OF AN YEAR:

Laughing

SET SERVEROUTPUT ON SIZE 4000

DECLARE
V_SAT DATE:=TO_DATE('14-JAN-06');
V_SUN DATE:=TO_DATE('15-JAN-06');
I NUMBER:=0;

BEGIN

LOOP
DBMS_OUTPUT.PUT_LINE('SATURDAY =>'||TO_CHAR(V_SAT,'DD-MON-RR')||'SUNDAY =>'||TO_CHAR(V_SUN,'DD-MON-RR'));
SELECT NEXT_DAY(TO_DATE('14-JAN-06')+I,'SATURDAY'),NEXT_DAY(TO_DATE('15-JAN-06')+I,'SUNDAY')
INTO V_SAT,V_SUN
FROM DUAL;
EXIT WHEN V_SAT > TO_DATE('01-JAN-07');
I:=I+7;
END LOOP;

END; Laughing

Report message to a moderator

Re: How to find the number of saturdays and Sundays? [message #195514 is a reply to message #195511]

Fri, 29 September 2006 02:47

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Or, if you fancy providing a suery like the OP asked for, you could use something like this:

select dte, to_char(dte,'DAY') day 
from (select to_date('01-jan-2006','dd-mon-yyyy') + level dte 
      from   dual 
      connect by level <= 365)
where to_char(dte,'DY') in ('SAT','SUN');

Report message to a moderator

Re: How to find the number of saturdays and Sundays? [message #195625 is a reply to message #195514]

Fri, 29 September 2006 13:59

sidagam
Messages: 24
Registered: February 2005
Location: Hyderabad

Junior Member

Hi All,

The query sent JRowbottom needs to change little bit.
It was not considering the 1st January,2006 which is comes on SUNDAY.

select
dte
, to_char(dte,'DAY') day
from
(
select
to_date('01-jan-2006','dd-mon-yyyy') + (level-1) dte
from dual
connect by level <= 365
)
where to_char(dte,'DY') in ('SAT','SUN');

Thanks & Regards,

Babu SRSB.

Report message to a moderator

Re: How to find the number of saturdays and Sundays? [message #195882 is a reply to message #195625]

Mon, 02 October 2006 21:18

ehegagoka
Messages: 493
Registered: July 2005

Senior Member

hi!
just wanna ask why using 'day' in the where clause does not return any value?


SQL> select dte, to_char(dte, 'day') day
  2  from (select to_date('01-jan-2006', 'dd-mon-rrrr') + (level-1) dte
  3  from dual
  4  connect by level <=14)
  5  where to_char(dte, 'dy') in ('sat', 'sun')
  6  /

DTE         DAY
----------- ---------
1/1/2006    sunday
1/7/2006    saturday
1/8/2006    sunday
1/14/2006   saturday

SQL> 

SQL> select dte, to_char(dte, 'day') day
  2  from (select to_date('01-jan-2006', 'dd-mon-rrrr') + (level-1) dte
  3  from dual
  4  connect by level <=14)
  5  where to_char(dte, 'day') in ('saturday', 'sunday')
  6  /

DTE         DAY
----------- ---------

SQL>

hhmmm i need to use trim to get it


SQL> select dte, to_char(dte, 'day') day
  2  from (select to_date('01-jan-2006', 'dd-mon-rrrr') + (level-1) dte
  3  from dual
  4  connect by level <=14)
  5  where trim(to_char(dte, 'day')) in ('saturday', 'sunday')
  6  /

DTE         DAY
----------- ---------
1/1/2006    sunday
1/7/2006    saturday
1/8/2006    sunday
1/14/2006   saturday

[Updated on: Mon, 02 October 2006 21:20]

Report message to a moderator

Re: How to find the number of saturdays and Sundays? [message #195893 is a reply to message #195882]

Tue, 03 October 2006 01:04

Maaher
Messages: 7065
Registered: December 2001

Senior Member

As you observed correctly, the 'day' date format is blank padded. The Oracle SQL Rerefence describes it as "Name of day, padded with blanks to length of 9 characters.". Oracle does have format modifiers to avoid the blank padding. 'FM' will avoid blank padding (this is also described in the SQL Reference).

SQL> select dte
  2       , to_char(dte, 'day') day
  3  from ( select  to_date('01-jan-2006', 'dd-mon-rrrr') + (level-1) dte
  4         from    dual
  5         connect by level <=14
  6       )
  7  where   to_char(dte, 'fmday') in ('saturday', 'sunday')
  8  /

DTE       DAY
--------- ---------
01-JAN-06 sunday
07-JAN-06 saturday
08-JAN-06 sunday
14-JAN-06 saturday

MHE

Report message to a moderator

Re: How to find the number of saturdays and Sundays? [message #195922 is a reply to message #195893]

Tue, 03 October 2006 03:04

ehegagoka
Messages: 493
Registered: July 2005

Senior Member

hi!
thanks sir!

Report message to a moderator

Previous Topic:	desired report
Next Topic:	Combining common rows and concatonating their ID

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 22:17:56 CST 2024