Solved

# How to get woring days between two date (Oracle)

Posted on 2010-01-05
Medium Priority
458 Views
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
Question by:zaki100
• 7
• 6
• 3
• +2

LVL 15

Expert Comment

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

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

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

LVL 15

Expert Comment

ID: 26180194

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

ID: 26180199
sorry

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

LVL 15

Expert Comment

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

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

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

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

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

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

Author Comment

ID: 26181560
Where to put table name?
0

LVL 78

Expert Comment

ID: 26182084
0

LVL 32

Expert Comment

ID: 26184175
See attached.
function.txt
0

LVL 14

Expert Comment

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

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

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

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

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
Course of the Month17 days, 12 hours left to enroll