Java Script Count the amount of working days since a date, find correct business day.

Rating: 4262 user(s) have rated this article Average rating: 5.0
Posted by: sergey, on 14/10/2008, in category "ASP.NET"
Views: this article has been read 43048 times
Location: United Kingdom
Abstract: Sometimes is very important to find when the next day of the delivery is or how many days business passed between current date and desirable date of our client to see his/her goods. Very often we need to develop client - validation (read: develop it in JavaScript). In this article I demonstrate two the most important functions for calculation business days between dates and find particular business day in the future. This program is very flexible and can be adapted to any country (because it is required to set up weekends days and holidays). At the end I will show how this data can be filled into MS SQL Server database (England UK public holidays till 2014).

This is just JavaScript. Please put it on your test web page, run and see results. After November 2008 you will need to change holiday string (addd new weekends and holidays dates).

 

// holidays as a long string with date presentation as YYYYMMDD
var holidays = '2008101820081019200810252008102620081101200811022008110820081109';
// The number of milliseconds in one day
var ONE_DAY = 1000 * 60 * 60 * 24;
 
function pageLoad() {
 
    document.write("<br /><b>Test for workingDayDiff: </b><br /><br />");
    var current_date = new Date();               
    for (var t = 1; t < 31; t++) {
        var new_date = new Date(current_date.valueOf() + ONE_DAY *t);
        var days_left = workingDayDiff(current_date, new_date);
        document.write('start date: ' + current_date.toDateString() + ' end date : ' + new_date.toDateString() + ' working days: ' + days_left.toString() + '<br />');
    }
 
 
    document.write("<br /><br /><b>Test for workingDayAdd: </b><br /><br />");
    for (var t = 1; t < 31; t++) {
        var requredDate = workingDayAdd(new Date(), t);
        document.write('start date: ' + (new Date()).toDateString() + ' days : ' + t.toString() + ' will be date: ' + requredDate.toDateString() + '<br />');
    }
 
}
 
function dayDiff(startDate, endDate) {
 
    var clearDate1 = new Date(startDate.getFullYear(), startDate.getMonth(), startDate.getDate());
    var clearDate2 = new Date(endDate.getFullYear(), endDate.getMonth(), endDate.getDate());
 
    // Calculate the difference in milliseconds
    var difference_ms = Math.abs(clearDate1 - clearDate2);
 
    // Convert back to days and return
    return Math.round(difference_ms / ONE_DAY);
}
 
 
function workingDayDiff(startDate, endDate) {
    // create pure dates without time
    // important to have 2 hours (because of Summer Time)
    var clearDate1 = new Date(startDate.getFullYear(), startDate.getMonth(), startDate.getDate(), 2, 0, 0);
    var clearDate2 = new Date(endDate.getFullYear(), endDate.getMonth(), endDate.getDate(), 2, 0, 0);
 
    // Calculate the difference
    var difference_ms = Math.abs(clearDate1 - clearDate2);
    var day_difference = Math.round(difference_ms / ONE_DAY);
 
    // exclude holidays
    var working_days = 0;
    // we assume that for holiday days qty of work days will be the same as for the next working day
    // please consider this assumption to your business rules
    // if you want make it the same as the previously business day
    // please use this : for (var i = 1; i <= day_difference; i++)
    for (var i = 0; i < day_difference; i++) {
        var checkDate = new Date(clearDate1.valueOf() + ONE_DAY * i);
        var checkString = checkDate.getFullYear().toString() +
        (checkDate.getMonth() + 1 < 10 ? '0' : '') + (checkDate.getMonth() + 1).toString() +
        (checkDate.getDate() < 10 ? '0' : '') + (checkDate.getDate()).toString();
        if (holidays.indexOf(checkString) == -1) {
            working_days++;
        }
    }
    return working_days;
}
 
 
function workingDayAdd(startDate, days) {
    // important to have 2 hours (because of Summer/Winter Time)
    var clearDate = new Date(startDate.getUTCFullYear(), startDate.getUTCMonth(), startDate.getUTCDate(), 2, 0, 0);
 
    var k = 0;
    // simple go throug all required days + extra holidays
    if (days >= 0) {
 
        for (var i = 0; i < days + 1000; i++) {
            var checkDate = new Date(clearDate.valueOf() + ONE_DAY * i);
 
            var checkString = checkDate.getUTCFullYear().toString() +
        (checkDate.getUTCMonth() + 1 < 10 ? '0' : '') + (checkDate.getUTCMonth() + 1).toString() +
        (checkDate.getUTCDate() < 10 ? '0' : '') + (checkDate.getUTCDate()).toString();
 
            // exclude holidays
            if (holidays.indexOf(checkString) == -1) {
                k++;
            }
 
            // exit if we have checked all days
            if (k > days) {
                break;
            }
        }
    }
    else {
        for (var i = 0; i > - 1000; i--) {
            var checkDate = new Date(clearDate.valueOf() + ONE_DAY * i);
 
            var checkString = checkDate.getUTCFullYear().toString() +
        (checkDate.getUTCMonth() + 1 < 10 ? '0' : '') + (checkDate.getUTCMonth() + 1).toString() +
        (checkDate.getUTCDate() < 10 ? '0' : '') + (checkDate.getUTCDate()).toString();
 
            // exclude holidays
            if (holidays.indexOf(checkString) == -1) {
                k++;
            }
 
            // exit if we have checked all days
            if (k > Math.abs(days)) {
                break;
            }
        }
    }
    return checkDate;
}

 

This is example how to fill data in the MS SQL Server.

Create table:

 

 CREATE TABLE [dbo].[HolidaysAndWeekends](

      [HolidaysAndWeekendsID] [int] IDENTITY(1,1) NOT NULL,
      [SiteID] [int] NOT NULL,
      [Holiday] smalldatetime NOT NULL,
      [BankHoliday] bit NOT NULL CONSTRAINT [DF_HolidaysAndWeekends_BankHoliday] DEFAULT (0)
 CONSTRAINT [PK_HolidaysAndWeekends] PRIMARY KEY CLUSTERED
(
      [HolidaysAndWeekendsID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX IX_HolidaysAndWeekends_SiteIDHoliday ON [dbo].[HolidaysAndWeekends](SiteID, Holiday)
GO
 

Fill weekends days and holidays dates into MS SQL Server database:

 

DECLARE @NEWSITEID AS Int
SET @NEWSITEID = 1
 
DECLARE @StartDate smalldatetime, @EndDate smalldatetime
SELECT @StartDate='2008-10-15'
SELECT @EndDate='2013-12-31'
 
WHILE @StartDate<@EndDate
 BEGIN
    SET @StartDate=@StartDate+1
    IF DATENAME(dw, @StartDate) = 'Sunday' OR DATENAME(dw, @StartDate) = 'Saturday'
      BEGIN
        INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,@StartDate,0 )
      END 
    CONTINUE
 END
-- add Bank Holidays England Only
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2008-12-25',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2008-12-26',1)
 
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2009-01-01',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2009-04-10',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2009-04-13',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2009-05-04',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2009-05-25',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2009-08-31',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2009-12-25',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2009-12-28',1)
 
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2010-01-01',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2010-04-02',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2010-04-05',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2010-05-03',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2010-05-31',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2010-08-30',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2010-12-27',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2010-12-28',1)
 
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2011-01-03',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2011-04-22',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2011-04-25',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2011-05-02',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2011-05-30',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2011-08-29',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2011-12-26',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2011-12-27',1)
 
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2012-01-02',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2012-04-06',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2012-04-09',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2012-05-07',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2012-05-28',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2012-08-27',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2012-12-25',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2012-12-26',1)
 
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2013-01-01',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2013-03-29',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2013-04-01',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2013-05-06',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2013-05-27',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2013-08-26',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2013-12-25',1)
INSERT INTO [dbo].[HolidaysAndWeekends]([SiteID], [Holiday], [BankHoliday]) VALUES (@NEWSITEID,'2013-12-26',1)
GO

 

Stored procedure which returns holidays as a long string (variant 1):

 

IF OBJECT_ID( N'dbo.site_GetHolidaysString', N'P' ) IS NOT NULL
 DROP PROCEDURE dbo.site_GetHolidaysString
GO
/*------------------------------------------------------------------------
 application: Web Site
      author: Sergey Chavlytko
     purpose: returns long string with weekends and
              banking holidays
             
 last edition: 15/10/2008
----------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[site_GetHolidaysString]
(
    @SiteID int
 , @StartDate smalldatetime
 , @Days int
 , @rVal varchar(4000) OUTPUT
 )
 
AS
BEGIN
      DECLARE @EndDate smalldatetime
 IF ISNULL(@Days,0)<=0 SET @Days=1
 SET @rVal=''
      SET @StartDate= convert(char(10), @StartDate, 20)
      SET @EndDate=     @StartDate+@Days
      SELECT @rVal=@rVal+convert(char(8), [Holiday], 112) FROM [dbo].[HolidaysAndWeekends]
       WHERE SiteID=@SiteID AND [Holiday] between @StartDate and @EndDate
END
GO

 

Stored procedure which returns holidays as a long string (variant 2):

 

IF OBJECT_ID( N'dbo.GetHolidaysString', N'P' ) IS NOT NULL
 DROP PROCEDURE dbo.GetHolidaysString
GO
/*------------------------------------------------------------------------
 application: Web site
      author: Sergey Chavlytko
     purpose: returns long string with weekends and
                   banking holidays
             
 last edition: 13/10/2008
----------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[GetHolidaysString]
(
    @SiteID int
 , @StartDate smalldatetime
 , @Days int
 , @rVal varchar(4000) OUTPUT
 )
 
AS
BEGIN
 IF ISNULL(@Days,0)<=0 SET @Days=1
 SET @rVal=''
      DECLARE @EndDate smalldatetime, @TemporaryDate smalldatetime
 
      SET @StartDate= convert(char(10), @StartDate, 20)
      SET @EndDate=     @StartDate+@Days
      WHILE @StartDate <= @EndDate
       BEGIN
          SET @TemporaryDate=NULL
          SELECT @TemporaryDate=[Holiday] FROM [dbo].[HolidaysAndWeekends] WHERE SiteID=@SiteID AND [Holiday]=@StartDate
          IF @TemporaryDate IS NOT NULL SELECT @rVal=@rVal+convert(char(8), @TemporaryDate, 112)
          SET @StartDate=@StartDate+1
          CONTINUE
       END
END
GO

 

Now you only need during creating your ASP.NET web page generate JavaScript where you add holiday string from the database. I believe, you can do it very easy for your business logic validation.

Good luck! 

How would you rate this article?

User Feedback

Post your comment:
Name:
E-mail:
Comment:
Insert Cancel