MySQL Forums
Forum List  »  Stored Procedures

Re: Recursively use a function to exclude Weekends and National Holidays to find a due date
Posted by: Peter Brawley
Date: March 10, 2019 12:55PM

> I need to enter the year wise data in it every year.

You have to do it anyway---some holiday dates vary year to year, many from country to country.

But you can automate most of it, the basics with a calendar table, see that topic at, and holidays dates with simple logic like that implemented with these little php funcs for US holidays ...

function market_holiday_dates( $yrs ) {
  $yr = date('Y');  // THIS YEAR
  $a = array();
  $a[] = array("Year","New Years","MLK Day","President","Good Friday","Memorial",
               "4 July","Labor Day","Thanksgiving","Christmas"
  $ymd = "Y-m-d";
  for( $y=$yr; $y<$yr+$yrs; $y++ ) {
    $a[] = array(
            wknd_adj( date("$y-01-01") ),
            date( $ymd, strtotime("third monday of january $y") ), 
            date( $ymd, strtotime("third monday of February $y") ),
            add_days( date($ymd,easter_date($y)), -2 ),
            date( $ymd, strtotime("last monday of may $y") ),
            date( $ymd, strtotime("$y-07-04") ),
            date( $ymd, strtotime("first monday of september $y") ),
            date( $ymd, strtotime("fourth thursday of november $y") ),
            wknd_adj( date("$y-12-25") )            
  return $a;

function add_days( $date, $days ) {
  $timeStamp = strtotime( $date ); 
  $timeStamp+= 24 * 60 * 60 * $days;
  return date( "Y-m-d", mktime( 0, 0, 0, date("n",$timeStamp),date("j",$timeStamp), date("Y",$timeStamp) ) ); 

function wknd_adj( $d ) {
  if( date('N',strtotime($d))==6 ) return add_days( $d, -1 );
  elseif( date('N',strtotime($d))==7 ) return add_days( $d, 1 );
  else return $d;

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.