How to get woring days between two date (Oracle)

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
zaki100Asked:
Who is Participating?
 
shru_0409Commented:
--- 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
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
shru_0409Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:
sorry

SELECT date_col+LEVEL-1 FROM your_table CONNECT BY date_col+LEVEL-1 <=SYSDATE
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
zaki100Author Commented:
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
 
shru_0409Commented:
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
 
zaki100Author Commented:
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
 
shru_0409Commented:
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
 
shru_0409Commented:
replace :l_end to sysdate
sorry i give the entered date .....
0
 
zaki100Author Commented:
Where to put table name?
0
 
slightwv (䄆 Netminder) Commented:
0
 
awking00Commented:
See attached.
function.txt
0
 
shru_0409Commented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:

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
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.