Java Script Count the amount of working days since a date, find correct business day.
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?