OraFAQ Forum: SQL & PL/SQL » how to find the # of workings days

Home » SQL & PL/SQL » SQL & PL/SQL » how to find the # of workings days

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

how to find the # of workings days [message #3396]

Wed, 25 September 2002 04:14

Saraswathi
Messages: 3
Registered: September 2002

Junior Member

hi,
can i find the number of working days between two dates using a single SQL query? if yes, can u please tell me the query to do the same?

thank u,
regards,

saraswathi.

Report message to a moderator

Re: how to find the # of workings days [message #3399 is a reply to message #3396]

Wed, 25 September 2002 05:02

Mike
Messages: 417
Registered: September 1998

Senior Member

what is a working day?
Is christmas or new year a working day?

Report message to a moderator

clarification of working day [message #3401 is a reply to message #3396]

Wed, 25 September 2002 05:08

Saraswathi
Messages: 3
Registered: September 2002

Junior Member

all days other than saturday and sunday are treated as working days.

saraswathi.

Report message to a moderator

Re: how to find the # of workings days [message #3408 is a reply to message #3396]

Wed, 25 September 2002 09:15

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

 
SQL> SELECT
  2  (
  3     ( TO_NUMBER(TRUNC(sysdate,'D') - TRUNC(HIREDATE +6, 'D')))/7*5)      +
  4     MOD(7 - TO_NUMBER(TO_CHAR(HIREDATE, 'D')),6) +
  5     LEAST(TO_NUMBER(TO_CHAR(sysdate, 'D')) - 2,5) days FROM EMP;

      DAYS
----------
      5680
      5633
      5632
      5604
      5477
      5583
      5556
      4027
      5441

Report message to a moderator

Re: how to find the # of workings days [message #3454 is a reply to message #3396]

Fri, 27 September 2002 22:25

chs
Messages: 31
Registered: December 2001

Member

here is the query to find no of working days between 2 given days.

select sum(decode(to_char(sysdate+rownum,'dy'),'sun',0,'sat',0,1)) cnt_wrkdays
from tab
where rownum < (to_date('31-oct-2002') - sysdate) + 1

Report message to a moderator

Previous Topic:	Extracting Number from an Alphanumeric string
Next Topic:	input truncated to 1 character

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 28 19:45:59 CDT 2024