Home » SQL & PL/SQL » SQL & PL/SQL » Days between two dates excluding saturday and sunday
icon5.gif  Days between two dates excluding saturday and sunday [message #248506] Fri, 29 June 2007 04:39 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member

Hi all,

I want to find the days between the dates excluding saturday and sunday.If dates are 22-6-07 and 29-6-07 then I want to get 5
Re: Days between two dates excluding saturday and sunday [message #248509 is a reply to message #248506] Fri, 29 June 2007 04:52 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Try some think like that

select count(*) from 
  (select sysdate+rownum dates from  dual connect by rownum < (sysdate+15)-(sysdate))  
where to_char(dates,'dy')!='sat' and to_char(dates,'dy')!='sun'



--Yash

[Updated on: Fri, 29 June 2007 04:58] by Moderator

Report message to a moderator

Re: Days between two dates excluding saturday and sunday [message #248511 is a reply to message #248509] Fri, 29 June 2007 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select count(*) from 
  2    (select sysdate+rownum dates from  dual connect by rownum < (sysdate+15)-(sysdate))  
  3  where to_char(dates,'dy')!='sat' and to_char(dates,'dy')!='sun'
  4  /
     COUNT(*)
-------------
           14

1 row selected.

Seems wrong to me.

Regards
Michel
Re: Days between two dates excluding saturday and sunday [message #248516 is a reply to message #248506] Fri, 29 June 2007 05:21 Go to previous messageGo to next message
s.anandjha
Messages: 4
Registered: November 2006
Location: Mumbai
Junior Member
try this

declare
i number(2);
j date;
begin
j:=to_date ('1 jun 2007');
i:=0;
while to_date('30 jun 2007')>=j loop
if to_char(j,'dy')<>'sun' and to_char(j,'dy')<>'sat' then
i:=i+1;
end if;
j:=j+1;
end loop;
dbms_output.put_line(i);
end;
Re: Days between two dates excluding saturday and sunday [message #248518 is a reply to message #248511] Fri, 29 June 2007 05:30 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I get a different result to you Michel

  1  select count(*) from
  2        (select sysdate+rownum dates from  dual connect by rownum < (sysdate+15)-(sysdate))
  3*     where to_char(dates,'dy')!='sat' and to_char(dates,'dy')!='sun'
SQL> /

  COUNT(*)
----------
        10


SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

[Updated on: Fri, 29 June 2007 05:32]

Report message to a moderator

Re: Days between two dates excluding saturday and sunday [message #248519 is a reply to message #248518] Fri, 29 June 2007 05:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think what Michel is pointing out is that your solution only works some of the time.
Depending on what your NLS parameters are set to, Saturday and Sunday may well be Samedi and Dimanche.

Re: Days between two dates excluding saturday and sunday [message #248520 is a reply to message #248519] Fri, 29 June 2007 05:57 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Not my solution J Smile I should maybe have made it clearer that I was trying to be facetious. We should have a sarcasm smiley Very Happy
Re: Days between two dates excluding saturday and sunday [message #248521 is a reply to message #248516] Fri, 29 June 2007 06:22 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anand,

Please read and follow How to format your posts

Don't use PL/SQL when you can use SQL.

Your solution don't work:
SQL> declare
  2  i number(2);
  3  j date;
  4  begin
  5  j:=to_date ('1 jun 2007');
  6  i:=0;
  7  while to_date('30 jun 2007')>=j loop
  8  if to_char(j,'dy')<>'sun' and to_char(j,'dy')<>'sat' then
  9  i:=i+1;
 10  end if;
 11  j:=j+1;
 12  end loop;
 13  dbms_output.put_line(i);
 14  end; 
 15  /
while to_date('30 jun 2007')>=j loop
                       *
ERROR at line 7:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 5

Regards
Michel

[Updated on: Fri, 29 June 2007 06:22]

Report message to a moderator

Previous Topic: Size of Packege/Procedure
Next Topic: Dynamic SQL (Merged topics)
Goto Forum:
  


Current Time: Sun Dec 04 23:06:08 CST 2016

Total time taken to generate the page: 0.12997 seconds