By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

workdays with holidays

P: 1
In my Access database project, I need to determine deadline dates based on working days and excluding holidays. I have set up the table as instructed below. The code that was given is for calculating the number of days between two dates. What I need is the code that will give me a date based on a date and the number of days (can vary and be either positive or negative). Can this be modified to do this? I appreciate your help.


CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT
'Y')

Populate it with as many years as you'll ever need:

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

Set the non-working days:

UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')

Record any public holidays in the same way.

Now you can easily compute the number of working days between two
dates:

SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @first_date AND @second_date
AND workingday = 'Y' ;
Aug 6 '07 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,383
DateAdd("d", #, SomeDate) will give you the date after adding # of days.
Aug 6 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.