how to add days to date excluding weekends?

Pranav Pandey

Solved

Question

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 :)

04 Mar 2019

Pranav Pandey

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

See solution in context

update.docx

04 Mar 2019

assif_tiger

MVP

Pranav Pandey wrote:

[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

04 Mar 2019

1 reply

04 Mar 2019

Show thread

Hide thread

Pranav Pandey

assif_tiger wrote:

Pranav Pandey wrote:

[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

Hi assif_tiger,

Thanks for your reply . Actually my action containing the business logic is present in Core module. I believe i cannot use any JS function there. Need something that can be implemented on server side.

04 Mar 2019

Paulo Zacarias

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

04 Mar 2019

1 reply

04 Mar 2019

Show thread

Hide thread

Pranav Pandey

Paulo Zacarias wrote:

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

Hi Paulo Zacarias,

I was also thinking the same way as you suggested . :(

04 Mar 2019

Pranav Pandey

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

04 Mar 2019

Thriveni Kambham

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

06 Mar 2019

Alberto Ferreira

Pranav and Thriveni

Nice sugestions!!

06 Mar 2019

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines