0

first of all i know that this has been asked before, and there are some really good answers, but not 100% correct, since my reputation is low I can't post comments. My question is how to add days in MySQL excluding weekends, for example the day 1/8/2018 if we add 20 days the results should be 1/29/2018, most of the forum answers are like the following

SELECT StartDate,Expected Date,DATE_FORMAT( DATE_ADD(StartDate, INTERVAL Expected Date +

IF( (WEEK(StartDate) <> WEEK(DATE_ADD(StartDate, INTERVAL Expected Date DAY))) OR (WEEKDAY(DATE_ADD(StartDate, INTERVAL Expected Date DAY)) IN (5, 6)),
IF( Expected Date >0, 2, -2 ), 0) DAY ) , '%y-%m-%d') as FinalDate from table,

THIS WORKS but for low numbers where it will encounter just one weekend, I do change the formula to accept minus add days. I know the tricky part is around IF( Expected Date >0, 2, -2 ), where it add or delete more days if its weekend, but cant find a way to figure it out if there are more than 1 weekend in between.

i hope I make my self clear and some could help me.

Similar post but not 100% right Here

Thank you

pipechaves
  • 73
  • 1
  • 7
  • Did you try the second answer? – rlanvin Jan 12 '18 at 21:42
  • yeah didnt work, but im testing it right now changing %v to %U, and will test it shortly with also minus days to add. Will be back soon with the results – pipechaves Jan 12 '18 at 21:55
  • works perfect with the %U, now if we try -5 days, we have an issue :), stil working/testing results – pipechaves Jan 12 '18 at 22:04
  • Well after many tests the positive work using the %U, BUT only if you want to add days. If you want to take out days no luck , even if using DATE_SUB, if some one has a clue will be great. As example please use the "start" date 2018-1-1 – pipechaves Jan 15 '18 at 21:41

0 Answers0