adding days to start date

[Paul]

Hi, I have a web application that I need to add 3 days to the Now day, but
need to make sure that I skip weekends and holidays. For example if Now is
friday, 3 days + now should be tuesday, counting the current day as the first
day. Now would never occure on a weekend or holiday. Anyhow just wondering
if anyone has any ideas? Thanks.

 

[Mark Rae [MVP]]

Hi, I have a web application that I need to add 3 days to the Now day, but
need to make sure that I skip weekends and holidays. For example if Now
is
friday, 3 days + now should be tuesday, counting the current day as the
first
day. Now would never occur on a weekend or holiday. Anyhow just
wondering
if anyone has any ideas? Thanks.

It's a trivial matter to work out whether a DateTime variable relates to a
weekend or not by inspecting the DayOfWeek property:
http://msdn2.microsoft.com/en-us/library/system.datetime.dayofweek.aspx

However, bear in mind that weekends are not always Saturday and Sunday
everywhere in the world.

As for public holidays, these differ from country to country. I'm not aware
of anything in the .NET Framework which will return whether given DateTime
and CultureInfo variables relate to a public holiday or not, although
Microsoft already know this information since it's possible to add public
holidays for individual countries to Outlook...

Therefore, what I do is hold a database table listing public holidays for
the next few years against a given country identifier. Armed with that, what
you require is easy enough by adding one day to any given DateTime variable
and incrementing a local variable by one if the resulting DateTime isn't a
weekend and doesn't appear in the database table of public holidays for the
country that you're working with. As soon as the local variable has a value
of 3, you have your result.

 

[Paul]

Hi thanks for the detailed information. I only have to worry about holidays
and weekends in the US so this does simplify it. Sounds like I may need to
store holiday dates in a table as you did.
Thanks Paul.

 

[Mark Rae [MVP]]

Hi thanks for the detailed information. I only have to worry about
holidays
and weekends in the US so this does simplify it. Sounds like I may need
to
store holiday dates in a table as you did.

If it helps, this is my SQL Server function for returning the next working
day, given a country code and a starting day

CREATE FUNCTION fdtmNextWorkingDay
(
@pstrISOCountryCode char(2),
@pdtmStart smalldatetime
)
RETURNS smalldatetime
AS
BEGIN
DECLARE @blnWorkingDay bit
SET @blnWorkingDay = 0
WHILE @blnWorkingDay = 0
BEGIN
SET @pdtmStart = DATEADD(dd, 1, @pdtmStart)
IF (DATEPART(dw, @pdtmStart) BETWEEN 2 AND 6)
AND NOT EXISTS(SELECT * FROM trelPublicHoliday
WHERE sdtmDate = @pdtmStart
AND strISOCountryCode = @pstrISOCountryCode)
BEGIN
SET @blnWorkingDay = 1
END
END
RETURN @pdtmStart
END

 

[Paul]

Hi thanks for the additional information. I was just wondering if there is a
source on the web that shows all of the holidays for the next 10 years,
including holidays that might show up on a weekend in one year and a weekday
on another year.