dates and weekends with asp

[coolicus]

Can asp work out weekends (I guess using a calendar) so that I can write a script that works out deadlines taking into account only working days and making sure the deadline doesn`t fall on a weekend.

I basically need to write a small app that sets deadline dates as deadline 1 = date of input + 5 working days. Deadline 2 = date of input + 12 working days etc etc.

Thanks

 

[monksnake]

Are you going pull the information from a database?

If so, I recommend doing all this in your query.

<.

 

[coolicus]

I am going to use MSSQL to store it all.

The database needs to store the start date (which is date of entry) and then 10 deadline dates.

deadline 1 = startdate + 5 working days
deadine 2 = startdate + 12 working days
etc etc

To enter it into the DB I have a small CMS, they basically just enter into text boxes the title and desciption and the asp does the rest using now() to grab the start date and now() + 5 etc to do the dates, but obviously this ignore weekends.

 

[Sheco]

Both VBScript and MSSQL have a function named DateAdd()

The function takes 3 parameters: interval, quantity, and date.


You can choose weekday as the interval and it will automatically skip weekends.

 

[Sheco]

Oh, and if you decide to do it directly in your SQL statement then be aware that SQL Server uses GetDate() instead of Now()

 

[gmmastros]

>> You can choose weekday as the interval and it will automatically skip weekends.

Sheco, I've been playing around with this for a bit, and I can't seem to get it to work for me. Can you post an example? Please?

I was ablet to get this work in SQL Server by creating a function. You would then call this function to return the new date.

[COLOR=blue]Create[/color] [COLOR=#FF00FF]Function[/color] dbo.AddWeekdays
	(
	@StartDate [COLOR=#FF00FF]DateTime[/color],
	@DaysToAdd [COLOR=blue]Int[/color]
	)
Returns [COLOR=#FF00FF]DateTime[/color]
[COLOR=blue]As[/color]
[COLOR=blue]Begin[/color]

	[COLOR=blue]Declare[/color] @DaysAdded [COLOR=blue]Int[/color]
	[COLOR=blue]Set[/color] @DaysAdded = 0

	[COLOR=blue]While[/color] @DaysAdded < @DaysToAdd
		[COLOR=blue]Begin[/color]

			[COLOR=blue]If[/color] [COLOR=#FF00FF]DatePart[/color]([COLOR=#FF00FF]Weekday[/color], @StartDate) Between 1 And 5
				[COLOR=blue]Begin[/color]
					[COLOR=blue]Set[/color] @DaysAdded = @DaysAdded + 1
				[COLOR=blue]End[/color]

			[COLOR=blue]Set[/color] @StartDate = @StartDate + 1

		[COLOR=blue]End[/color]

	[COLOR=blue]Return[/color] @StartDate

[COLOR=blue]End[/color]

You would call it like this...

Select StartDate,
       dbo.AddWeekdays(StartDate, 5) As Deadline1,
       dbo.AddWeekdays(StartDate, 12) As Deadline2
From   [!]TableName[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

*Disclaimer, this function may not be 100% correct syntax, but the idea is correct.

Here's a function that will allow you to figure out, you will need to pass it the startDate (which will always be now() and the number of weekdays you want added to it)

Function addWeekdays(num, startDate)
   Dim a = 0
   While a < num
      startDate = DateAdd("d",1,startDate"))
      If DatePart("w",startDate) <> 1 And DatePart("w",startDate) <> 7 Then 
         a += 1
      End If
   End While
   addWeekdays = startDate
End Function

<.

 

[monksnake]

You can choose weekday as the interval and it will automatically skip weekends

I tested that and I didn't see it. But it does seem weird to have a dateadd day function and a weekday function if they are both going to do the same thing.


<.

 

[Sheco]

Are you using SQL Server 2005 ?

 

[Sheco]

/blush

I'm wrong, weekday does the same thing as day.

 

[coolicus]

I have googled DateAdd() and found this, is this the thing I am looking for?

http://www.codeproject.com/asp/BusinessDates.asp

No idea how to use it though hehe

 

[monksnake]

You have 2 direct examples higher up in the thread. I think that's what you'd be looking for.

<.

 

[coolicus]

oops sorry, I didn`t refresh before my new post

 

[Tarwn]

Here is a version of a previously posted function that does the calculation without any looping:

Function calcEndDateLong(startDate,numOfDays)
	Dim numDaysToAdd
	[COLOR=#008000]'add two days for each 5 business days[/color]
	numDaysToAdd = numOfDays + numOfDays + (2 * Fix(numOfDays/5))
	[COLOR=#008000]'add two more days if business day count will force an extra weekend[/color]
	[COLOR=#008000]'	Example: 4/9/13/etc business days on a friday causes an extra weekend[/color]
	[COLOR=#008000]'		Fri - 4, 3, 2, 1 causes extra weekend[/color]
	[COLOR=#008000]'		Thu - 3, 2, 1[/color]
	[COLOR=#008000]'		Wed - 2, 1[/color]
	[COLOR=#008000]'		Tue - 1[/color]
	numDaysToAdd = numDaysToAdd + Fix((WeekDay(startDate) mod 7 + (numOfDays mod 5))/7) * 2

	[COLOR=#008000]'add those days in[/color]
	calcEndDate = DateAdd("d",numDaysToAdd,startDate)

	[COLOR=#008000]'If saturday and number of days was evenly divisble by 5, subtract a day to remove extra weekend round off[/color]
	[COLOR=#008000]'If saturday and number of days not evenly divisble by 5, add a day to fix round off[/color]
	If WeekDay(startDate) = 7 Then  calcEndDate = DateAdd("d",Fix((numOfDays mod 5 + 4)/5) * 2 - 1,calcEndDate)
	[COLOR=#008000]'Same with sunday, except it is subtract 2 on evenly divisble and nothing on other amt's[/color]
	If WeekDay(startDate) = 1 Then  calcEndDate = DateAdd("d",Fix((numOfDays mod 5 + 4)/5) * 2 - 2,calcEndDate)
End Function

Sorry if there is some odd math in there, I never did fully clean it up after trying to condense it down to a one liner, so it is a little convoluted.
The previous function (in one line) and another loop-based example are located here: thread333-825874

Best MS KB Ever:

http://support.microsoft.com/kb/555375