1

Excel has the WORKDAY() function that calculates the next working day given a number of days including holidays.

I've been trying to do an equivalent efficient function in MySQL but dealing with the exceptions was too much and I'm stuck with this. Have anybody found an efficient solution for this?

The Excel WORKDAY Function

WORKDAY(start_date, day_count [,holiday_list])

This works like start_date + INTERVAL day_count DAY except that it only includes workdays. Workdays are defined as non-weekend non-holiday days. Weekends here are Sa and Su.

WORKDAY.INTL(start_date,  day_count [,weekend [,holiday_list]])

Is the same except weekend is a code identifying which days of the week constitute the weekend.

Community
  • 1
  • 1
kira_zee
  • 11
  • 2
  • 2
    no such beast. many holidays are not constant (e.g. easter, labor day), and holidays differ from region to region. there is no mysql function for this, and writing one in sql will be extraordinarily ugly. – Marc B Jan 04 '13 at 14:37
  • I've been trying with a calendar table where I have all days and two binary fields (weekday, holiday) so I can easily choose what is a holiday in my region... the possibilities of starting, ending and having holidays and not working days in the middle are killing me! – kira_zee Jan 04 '13 at 14:44
  • "no such beast" -- very strong statement. There are all kinds of ways of doing this. Doing it right might generate tremendous added value to some application. The Excel way seems a little crude to me. @kira_zee, can you tell us more about how you hope to tell your application what the holidays and weekends are? Maybe post an example of your calendar table? – O. Jones Jan 04 '13 at 15:18
  • Thx Ollie, I agree ... it might be a beast... but there should be a way to make this with a pure SQL approach. My table just has 3 fields: (date, isweekday, isholiday) ex: (2012-06-03, 0, 0)... the algorythm that reeds that table and counts the right number of working days and returns the future workingday... that's the real problem. – kira_zee Jan 04 '13 at 15:52

1 Answers1

0

Do a holiday table that includes the year and flag the most-likely holidays for that year with their specific day/date, then reference that yearly holiday table in your MySQL function.

Since calendars eventually repeat, you should be able to write something to populate the holiday table based on yearly repetition.

Barry
  • 3,303
  • 7
  • 23
  • 42
RHamblin
  • 43
  • 9
  • I already did that, my table looks like this, is already populated and with real data till 2014 date isweekday isholiday . . . 2012-06-03 0 0 2012-06-04 1 0 . . The real problem right now is with the function, because no matter the day you choose (not weekday, holiday, weekday) and no matter the ammount of days, it should give you the right answer, I'm stuck at the exceptions... – kira_zee Jan 04 '13 at 15:41
  • This question and answer may be of some use. http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates?rq=1 – RHamblin Jan 04 '13 at 16:30
  • @RHamblin not that much as they are two different questions... this is to count working days given two dates; the other is to calculate a second date given one date and a number of working days. While the former can be used to validate the later it would be a brute force approach testing each date pair until the resulting working days matched – Barry Sep 23 '21 at 08:37