?
Solved

How to get woring days between two date (Oracle)

Posted on 2010-01-05
18
Medium Priority
?
458 Views
Last Modified: 2013-12-07
Hello,

I need to use select statement in oracle to get working days (.e.g from Sunday to Friday) between two date. The fist date is a values in a field and the second date is systdate

Thanks

Zaki
0
Comment
Question by:zaki100
  • 7
  • 6
  • 3
  • +2
18 Comments
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26180176
EG:

SELECT TO_DATE('01-JAN-10','DD-MON-YY')+LEVEL-1 FROM DUAL CONNECT BY TO_DATE('01-JAN-10','DD-MON-YY')+LEVEL <=SYSDATE;
 
 
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 26180183
SELECT CASE
          WHEN LEVEL = 1 THEN TRUNC(d, 'mm')
          ELSE NEXT_DAY(TRUNC(d, 'mm'), 'Sunday') + (7 * (LEVEL - 2))
       END begindate,
       LEAST(NEXT_DAY(CASE  WHEN LEVEL = 1 THEN TRUNC(d, 'mm') - 1  ELSE
                             NEXT_DAY(TRUNC(d, 'mm'), 'Sunday') + (7 * (LEVEL - 2)) END,'Friday'),
             TRUNC(LAST_DAY(d))) enddate
      FROM (SELECT sysdate d FROM DUAL)
CONNECT BY NEXT_DAY(TRUNC(d, 'mm'), 'Monday') + (7 * (LEVEL - 2)) <=
               LAST_DAY(d)


try this
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26180187
if u want to include current date use

SELECT TO_DATE('01-JAN-10','DD-MON-YY')+LEVEL-1 FROM DUAL CONNECT BY TO_DATE('01-JAN-10','DD-MON-YY')+LEVEL-1 <=SYSDATE
 
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26180194
or from your table

SELECT date_col+LEVEL-1 FROM your_table CONNECT BY TO_DATE(date_col,'DD-MON-YY')+LEVEL-1 <=SYSDATE
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26180199
sorry

SELECT date_col+LEVEL-1 FROM your_table CONNECT BY date_col+LEVEL-1 <=SYSDATE
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26180561
if u want to exclude sundays and saturdays
pls use

SELECT date_col+LEVEL-1 FROM your_table where TRIM(date_col+level-1,'DAY')) NOT IN ('SUNDAY' ,'SATURDAY')
CONNECT BY date_col+LEVEL-1 <=SYSDATE ;
0
 

Author Comment

by:zaki100
ID: 26181247
shajukg:

This query gives error:
SELECT date_col+LEVEL-1 FROM your_table where TRIM(date_col+level-1,'DAY')) NOT IN ('SUNDAY' ,'SATURDAY')
CONNECT BY date_col+LEVEL-1 <=SYSDATE ;

Error: Missing right  parenthesis

Thanks
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 26181258
SELECT date_col+LEVEL-1 FROM t1 where to_char((date_col+level-1),'DAY')  NOT IN ('SUNDAY' ,'SATURDAY')
CONNECT BY date_col+LEVEL-1 <=SYSDATE ;

try this but this will retur todays date
0
 

Author Comment

by:zaki100
ID: 26181377
No. This quey returned a date as you said. I need the query to return number of days with exclusion of Saturday and friday.

Thanks

Zaki
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 26181447
SELECT COUNT(*)
          FROM (SELECT d
                  FROM (SELECT  :l_start + LEVEL - 1 d
                              FROM DUAL
                        CONNECT BY LEVEL <=(:l_end - :l_start + 1))
                 WHERE TO_CHAR(d, 'Dy') NOT IN('Fri', 'Sat'))


try this

0
 
LVL 14

Expert Comment

by:shru_0409
ID: 26181501
replace :l_end to sysdate
sorry i give the entered date .....
0
 

Author Comment

by:zaki100
ID: 26181560
Where to put table name?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26182084
0
 
LVL 32

Expert Comment

by:awking00
ID: 26184175
See attached.
function.txt
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 26187346
this query will give the count of the days excluding Fri & Sat

SELECT COUNT(*)
          FROM (SELECT d
                  FROM (SELECT  :l_start + LEVEL - 1 d
                              FROM table_name
                        CONNECT BY LEVEL <=(:l_end - :l_start + 1))
                 WHERE TO_CHAR(d, 'Dy') NOT IN('Fri', 'Sat'))


this query will give the date ....

          SELECT d
                  FROM (SELECT  :l_start + LEVEL - 1 d
                              FROM table_name
                        CONNECT BY LEVEL <=(:l_end - :l_start + 1))
                 WHERE TO_CHAR(d, 'Dy') NOT IN('Fri', 'Sat')

0
 
LVL 14

Accepted Solution

by:
shru_0409 earned 2000 total points
ID: 26187651
--- count of days

select count(*) from( SELECT d
                  FROM (SELECT  DATE_COL + LEVEL - 1 d
                              FROM your_table
                        CONNECT BY LEVEL <=(sysdate - DATE_COL + 1))
                 WHERE TO_CHAR(d, 'Dy') NOT IN('Fri', 'Sat') )

-- display date
SELECT d
                  FROM (SELECT  DATE_COL + LEVEL - 1 d
                              FROM your_table
                        CONNECT BY LEVEL <=(sysdate - DATE_COL + 1))
                 WHERE TO_CHAR(d, 'Dy') NOT IN('Fri', 'Sat')

try this
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26187920

SELECT date_col+LEVEL-1 FROM your_table where to_char(TRIM(date_col+level-1),'DAY') NOT IN ('SUNDAY' ,'SATURDAY')
CONNECT BY date_col+LEVEL-1 <=SYSDATE ;
 

SELECT count(1)  FROM your_table where to_char(TRIM(date_col+level-1),'DAY') NOT IN ('SUNDAY' ,'SATURDAY')
CONNECT BY date_col+LEVEL-1 <=SYSDATE ;
 


0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26188421
Try this
 

CREATE TABLE YOUR_TABLE
(
A_NUM NUMBER(10),
B_DT DATE,
C_NUM NUMBER(12)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
 
 
 
Insert into YOUR_TABLE
(A_NUM, B_DT, C_NUM)
Values
(1, TO_DATE('01/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 50);
Insert into YOUR_TABLE
(A_NUM, B_DT, C_NUM)
Values
(2, TO_DATE('01/02/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 60);
COMMIT;
 
 
WITH Q AS (SELECT B_DT FROM YOUR_TABLE WHERE A_NUM=2)
SELECT B_DT+LEVEL-1 FROM Q where TRIM(TO_CHAR(B_DT+level-1,'DAY')) NOT IN ('SUNDAY' ,'SATURDAY')
CONNECT BY B_DT+LEVEL-1 <=SYSDATE ;
WITH Q AS (SELECT B_DT FROM YOUR_TABLE WHERE A_NUM=2)
SELECT count(*) FROM Q where TRIM(TO_CHAR(B_DT+level-1,'DAY')) NOT IN ('SUNDAY' ,'SATURDAY')
CONNECT BY B_DT+LEVEL-1 <=SYSDATE ;
 
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question