Home » SQL & PL/SQL » SQL & PL/SQL » How to find the number of saturdays and Sundays?
How to find the number of saturdays and Sundays? [message #195495] Fri, 29 September 2006 01:29 Go to next message
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..

icon10.gif  Re: How to find the number of saturdays and Sundays? [message #195511 is a reply to message #195495] Fri, 29 September 2006 02:23 Go to previous messageGo to next message
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
Re: How to find the number of saturdays and Sundays? [message #195514 is a reply to message #195511] Fri, 29 September 2006 02:47 Go to previous messageGo to next message
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');
Re: How to find the number of saturdays and Sundays? [message #195625 is a reply to message #195514] Fri, 29 September 2006 13:59 Go to previous messageGo to next message
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.
Re: How to find the number of saturdays and Sundays? [message #195882 is a reply to message #195625] Mon, 02 October 2006 21:18 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: How to find the number of saturdays and Sundays? [message #195922 is a reply to message #195893] Tue, 03 October 2006 03:04 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thanks sir!
Previous Topic: desired report
Next Topic: Combining common rows and concatonating their ID
Goto Forum:
  


Current Time: Sat Dec 10 14:39:21 CST 2016

Total time taken to generate the page: 0.06873 seconds