How to get 14 days prior to the given date avoiding holidays

How to get 14 days prior to the given date avoiding holidays

In my system ,the due date of the bill must be 14 days after the issued date.
I have due date and I want to know issued date .
I have to calculate :

issued date = 14 days prior to the due date  

but 14 days must be business days ,not holidays.
Holidays is stored in a table 'tblHolidayMaster' like this,

DateDescription
2012/05/13Mother's Day
2012/06/02Saturnday
2012/12/25Christmas

How can I calculate the issued date avoiding holidays?
Thank you for all of your interests and replies.

Answer by Rajesh Subramanian for How to get 14 days prior to the given date avoiding holidays

Try the following link,

http://www.c-sharpcorner.com/uploadfile/tirthacs/difference-between-two-dates-excluding-weekends/

Answer by Ketchup for How to get 14 days prior to the given date avoiding holidays

I would calculate the Date using a function like the one below (which i use)

public static DateTime AddBusinessDays(DateTime date, int days)   {      if (days == 0) return date;       if (date.DayOfWeek == DayOfWeek.Saturday)     {      date = date.AddDays(2);      days -= 1;    }    else if (date.DayOfWeek == DayOfWeek.Sunday)    {      date = date.AddDays(1);      days -= 1;    }          date = date.AddDays(days / 5 * 7);   int extraDays = days % 5;     if ((int)date.DayOfWeek + extraDays > 5)   {      extraDays += 2;   }    int extraDaysForHolidays =-1;  //Load holidays from DB into list  List dates = GetHolidays();    while(extraDaysForHolidays !=0)  {     var days =  dates.Where(x => x >= date  && x <= date.AddDays(extraDays)).Count;   extraDaysForHolidays =days;   extraDays+=days;    }      return date.AddDays(extraDays);  

}

Haven't tested the ast section that does the holidays

Answer by CodesInChaos for How to get 14 days prior to the given date avoiding holidays

I went with the straight forward looping solution, so it will be slow for long intervals. But for short intervals like 14 days, it should be quite fast.

You need to pass in the holidays in the constructor. An instance of BusinessDays is immutable and can be reused. In practice you probably will use an IoC singleton or a similar construct to get it.

AddBusinessDays throws an ArgumentException if the start date is a non business day, since you didn't specify how to treat that case. In particular AddBusinessDays(0) on a non business day would have strange properties otherwise. It'd either break time reversal symmetry, or return a non business day.

public class BusinessDays  {      private HashSet holidaySet;      public ReadOnlyCollection WeekendDays{get; private set;}        public BusinessDays(IEnumerable holidays, IEnumerable weekendDays)      {          WeekendDays = new ReadOnlyCollection(weekendDays.Distinct().OrderBy(x=>x).ToArray());          if(holidays.Any(d => d != d.Date))              throw new ArgumentException("holidays", "A date must have time set to midnight");          holidaySet = new HashSet(holidays);      }        public BusinessDays(IEnumerable holidays)          :this(holidays, new[]{DayOfWeek.Saturday, DayOfWeek.Sunday})      {      }        public bool IsWeekend(DayOfWeek dayOfWeek)      {          return WeekendDays.Contains(dayOfWeek);      }        public bool IsWeekend(DateTime date)      {          return IsWeekend(date.DayOfWeek);      }        public bool IsHoliday(DateTime date)      {          return holidaySet.Contains(date.Date);      }        public bool IsBusinessDay(DateTime date)      {          return !IsWeekend(date) && !IsHoliday(date);      }        public DateTime AddBusinessDays(DateTime date, int days)      {          if(!IsBusinessDay(date))              throw new ArgumentException("date", "date bust be a business day");          int sign = Math.Sign(days);          while(days != 0)          {              do              {                date.AddDays(sign);              } while(!IsBusinessDay(date));              days -= sign;          }          return date;      }  }  

Answer by Talha for How to get 14 days prior to the given date avoiding holidays

I think that is what you required. It is simple and I have tested it and it is working... And it is not a bad approach to write a function or SP in databases rather to write the complex code in C#... (change column name of date as in your db.)

Make it function or SP as what you want.

Note: Comment the check of 'Saturday' and 'Sunday'. If it is already added in your table reocrds.

declare @NextWorkingDate datetime  declare @CurrentDate datetime  set @CurrentDate = GETDATE()  set @NextWorkingDate = @CurrentDate   declare @i int = 0  While(@i < 14)  Begin      if(((select COUNT(*) from dbo.tblHolidayMaster where convert(varchar(10),[Date],101) like convert(varchar(10),@NextWorkingDate,101)) > 0) OR DATENAME(WEEKDAY,@NextWorkingDate) = 'Saturday' OR DATENAME(WEEKDAY,@NextWorkingDate) = 'Sunday')      Begin          print 'a '            print @NextWorkingDate          set @NextWorkingDate = @NextWorkingDate + 1          CONTINUE      End      else      Begin          print  'b '           print @NextWorkingDate          set @NextWorkingDate = @NextWorkingDate + 1          set @i = @i + 1          CONTINUE      End  End  print @NextWorkingDate   

Answer by MonMon for How to get 14 days prior to the given date avoiding holidays

I calculate the issued date avoid your holiday from your table 'tblHolidayMaster' only.

 int addDay = -14;    DateTime dtInputDay = System.DateTime.Now;//Your input day    DateTime dtResultDate = new DateTime();    dtResultDate = dtInputDay.AddDays(addDay);    bool result = false;    string strExpression;    DataView haveHoliday;    while (!result) {        strExpression = "Date >='" + Convert.ToDateTime(dtResultDate.ToString("yyyy/MM/dd")) + "' and Date <='" + Convert.ToDateTime(dtInputDay.ToString("yyyy/MM/dd")) + "'";        haveHoliday = new DataView(tblHolidayMaster);        haveHoliday.RowFilter = strExpression;        if (haveHoliday.Count == 0) {                    result = true;        } else {            addDay = -(haveHoliday.Count);            dtInputDay = dtResultDate.AddDays(-1);            dtResultDate = dtResultDate.AddDays(addDay);        }    }                        

Your issued date is dtResultDate

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72