-
problem with query Oracle
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|