AnsweredAssumed Answered

SQL due date query issue, excluding weekend

Question asked by Anjan kumar kaleru on Jan 11, 2017
Latest reply on Jan 11, 2017 by Anjan kumar kaleru

Hi,

 

I'm using workflows and I need to set due date as 56 business hours from the date request has been created ( 7 business days).

Initially I used. 

to_char(cr.request_date+7, 'YYYY-MM-DD HH24:MI:SS') as due_date

which only sets due date after 7 days, which doesn't exclude weekends as it counts weekend as a day too.

Later. I've used this.

select request_date + (select max(rnum) seven_business_days_from_now from
(select rownum rnum from all_objects) where rownum <=7
and to_char(to_date('01/17/2004','mm/dd/yyyy') + rnum, 'dy','NLS_DATE_LANGUAGE=AMERICAN')
not in ('sat','sun')) as delivery_date from t_av_change_requests where id=104

Still it generates random dates. May i know where I'm going wrong?

Outcomes