Hi,
I've table TAB_DATE:

COD_ID............DATE_C..............DATE_R
1................3/3/2005.............1/3/2005
2................7/3/2005.............1/3/2005
3................7/3/2005.............3/3/2005
4................9/3/2005.............1/3/2005
5................19/3/2005............14/3/2005
6................28/2/2005.............7/2/2005
7................15/3/2005.............9/3/2005
8................15/3/2005.............1/3/2005
9................15/3/2005............11/3/2005


I want to create an Oracle query, so that the difference in time should be based on business day. (i.e. Saturday and Sunday must be excluded.)

for example DATE_C = 15/3/2005
DATE_R = 11/3/2005
difference = 2

DATE_C = 15/3/2005
DATE_R = 9/3/2005
difference = 4

DATE_C = 15/3/2005
DATE_R = 1/3/2005
difference = 10

DATE_C = 28/2/2005
DATE_R = 7/2/2005
difference = 15



if I run:
SELECT to_date( DATE_C,'dd/mm/yyyy') DATE_C
FROM TAB_DATE
where rownum=1

COD_ID............DATE_C
1................3/3/0005

or

SELECT to_date( DATE_C,'dd/mm/yy') DATE_C
FROM TAB_DATE
where rownum=1

COD_ID............DATE_C
1................3/3/2005


I tried this query:

select COD_ID,DATE_C,DATE_R,trunc((to_date(DATE_C,'dd/mm/yy') -
to_date(DATE_R,'dd/mm/yy')+1)/7)*5 +
length(replace(substr('012345001234560',to_char(to _date(DATE_R,'dd/mm/yy'),'d'),
mod(to_date(DATE_C,'dd/mm/yy') - to_date(DATE_C,'dd/mm/yy')+1,7)),'0','')) difference
from tab_date

but I get this output:


COD_ID............DATE_C..............DATE_R...... .......DIFFERENCE
1................3/3/2005.............1/3/2005...............2
2................7/3/2005.............1/3/2005...................
3................7/3/2005.............3/3/2005...............4
4................9/3/2005.............1/3/2005...............6
5................19/3/2005............14/3/2005..............4
6................28/2/2005.............7/2/2005.................
7................15/3/2005.............9/3/2005.................
8................15/3/2005.............1/3/2005.................
9................15/3/2005............11/3/2005..............3

or

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY'

and I tried:

select COD_ID,DATE_C,DATE_R,trunc((to_date(DATE_C,'dd/mm/yyyy') -
to_date(DATE_R,'dd/mm/yyyy')+1)/7)*5 +
length(replace(substr('012345001234560',to_char(to _date(DATE_R,'dd/mm/yyyy'),'d'),
mod(to_date(DATE_C,'dd/mm/yyyy') - to_date(DATE_C,'dd/mm/yyyy')+1,7)),'0','')) difference
from tab_date

but I get this output:

COD_ID............DATE_C..............DATE_R...... .......DIFFERENCE
1................3/3/2005.............1/3/2005...............3
2................7/3/2005.............1/3/2005...................
3................7/3/2005.............3/3/2005...............3
4................9/3/2005.............1/3/2005...............7
5................19/3/2005............14/3/2005..............5
6................28/2/2005.............7/2/2005..............16
7................15/3/2005.............9/3/2005.................
8................15/3/2005.............1/3/2005..............11
9................15/3/2005............11/3/2005..............3


How Can I write my query to get the difference between date without Saturday and Sunday?

Thanks in advance!