The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Sysdate plus weekdays

SOLVED
Go to solution
Highlighted
patrick-sa
Regular Collector

Sysdate plus weekdays

Hi,
I need a query to populate the sla violation date by adding working days to the sysdate.The query below includes saturday and sunday which i want to exclude.

select sysdate+5 from dual

I'm looking for a query where i can add any number of days to the sysdate while excluding saturdays and sundays.

Thanks.
9 REPLIES
Mahen M
Honored Contributor

Re: Sysdate plus weekdays

Try to modify the below Query to your req

select dates,To_char(dates,'DAY') DAYs FROM
(
select trunc(sysdate,'MM') + level -1 Dates from dual connect by
level <= ADD_MONTHS(trunc(sysdate,'MM'),1)-1 - trunc(sysdate,'MM')+1
) Where To_char(dates,'DY') NOT IN ('SAT','SUN')

Regards,
Mahendran M
patrick-sa
Regular Collector

Re: Sysdate plus weekdays

Hi Rithi,

Thanks for this,but if you don't mind pls show me how to do this.the query you've just sent returns weekdays and the date but i can't figure out how to add the sysdate plus 4 days and exclude saturday and sunday.

thanks
Mahen M
Honored Contributor
Solution

Re: Sysdate plus weekdays

select dates FROM
(
select (sysdate + level+1) +100 Dates from dual connect by
level <= ADD_MONTHS(trunc(sysdate,'YY'),1)-1 - trunc(sysdate,'YY')+1
) Where To_char(dates,'DY') NOT IN ('SAT','SUN')
And Rownum <=1


Replace 100 with your Token



Regards,
Mahendran M
patrick-sa
Regular Collector

Re: Sysdate plus weekdays

Hi Rithi,

Thanks.Have a great year ahead!
patrick-sa
Regular Collector

Re: Sysdate plus weekdays

Hi Rithi,

The query works perfectly for days above 5,but when i replace 100 with 1 the date returned is 14th january which is wrong, 2,3 and 4 indicates the 17th january which is also wrong.

please advise when you can.
Mahen M
Honored Contributor

Re: Sysdate plus weekdays

Give me some time please... If it works for days greater than 5 days, We can handle it with case or decode statement.

Regards,
Mahendran M
patrick-sa
Regular Collector

Re: Sysdate plus weekdays

Hi Rithi,

Ok.i'm not in a hurry i only need the solution early next week.i don't mind a decode or case statement whichever works for 1 to 4 days.

thanks
Jim Esler
Honored Contributor

Re: Sysdate plus weekdays

You could use the Business Date function listed at http://psoug.org/reference/date_func.html. They also have a variant that handles negative values. It is not elegant or overly clever but it works.
patrick-sa
Regular Collector

Re: Sysdate plus weekdays

Hi Jim,

Thanks.this solves my problem.
//Add this to "OnDomLoad" event