1103
Views
7
Comments
Solved
how to add days to date excluding weekends?

Currently I have a scenario where I need to add only working days to get the next date (that is excluding weekends). Is there any function available which I can use / or any logic that i can implement ??

[I cannot create or add any extension to the project as the current organization don't allow me to do so]

I know one method of looping and check each days and then counting but It don't seems to be proper ways , so other suggestions would be helpful :)

Rank: #397
Solution

Thanks all for your help , really appreciated . 

I found a simple and very effective way to do it .

I have taken help of Advance sql query to calculate the next working day

pass two parameters @MyDate ,@MyDays and define a structure of type Date and execute the below query


SELECT
        DATEADD(d
        , (@MyDays / 5) * 7
          + (@MyDays % 5)
          + (CASE WHEN ((@MyDays%5) + DATEPART(dw, @MyDate)) IN (1,7,8,9,10) THEN 2 ELSE 0 END)
        , @MyDate) AS Date


Never though I can make use of sql like this. :D 

update.docx

Rank: #82

Pranav Pandey wrote:

Currently I have a scenario where I need to add only working days to get the next date (that is excluding weekends). Is there any function available which I can use / or any logic that i can implement ??

[I cannot create or add any extension to the project as the current organization don't allow me to do so]

I know one method of looping and check each days and then counting but It don't seems to be proper ways , so other suggestions would be helpful :)

Hi,

One Of the approach you can use is below snippet

var startDate = "9-DEC-2011";
startDate = new Date(startDate.replace(/-/g, "/"));
var endDate = "", noOfDaysToAdd = 13, count = 0;
while(count < noOfDaysToAdd){
    endDate = new Date(startDate.setDate(startDate.getDate() + 1));
    if(endDate.getDay() != 0 && endDate.getDay() != 6){
       //Date.getDay() gives weekday starting from 0(Sunday) to 6(Saturday)
       count++;
    }
}
alert(endDate);//You can format this date as per your requirement

Thanks

Rank: #396

Hello Pranav, 

You can use the DayOfWeek function, it's a built-in function:

And add some logic to do what you want. 

Let us know if you managed to solve it.

Best Regards

Rank: #397
Solution

Thanks all for your help , really appreciated . 

I found a simple and very effective way to do it .

I have taken help of Advance sql query to calculate the next working day

pass two parameters @MyDate ,@MyDays and define a structure of type Date and execute the below query


SELECT
        DATEADD(d
        , (@MyDays / 5) * 7
          + (@MyDays % 5)
          + (CASE WHEN ((@MyDays%5) + DATEPART(dw, @MyDate)) IN (1,7,8,9,10) THEN 2 ELSE 0 END)
        , @MyDate) AS Date


Never though I can make use of sql like this. :D 

update.docx

Rank: #17967

Hi Pranav, 

Check the Below Advance Query which may help you to  get next date by passing currentdate and NoofDays .. Nextday and isweekend Paramters are using for my internal logic

Pranav and Thriveni

Nice sugestions!!