calculate weekendings for a date range

All,
I have stored procedure that accepts 2 parameters (StartDate and EndDate). I need a way to find out which weekending dates fall between the two parameters .

For example :

StartDate: 02-jan-2006
EndDate: 19-jan-2006

Therefore the weekending dates would be 8-Jan 2006 and 15-jan-2006

Can anyone help

Cheers

Hi,

look here:

http://www.aspfaq.com/show.asp?id=2519

HTH, Jens Suessmeyer.

|||

You can compute the last "end of week" based on the day-of-week DatePart() value. From this, you can also compute the next "end of week". This will all be consistent with the @.@.DateFirst parameter setting the first day of the week -- hence moving the actual day associated with the "end of week".

To find the last "end of week", subtract days equal to 1 less than the DatePart() value. To find the next "end of week", do as above, but add 7 days modulo 7 -- which won't change the date if the end of week is originally supplied.

Declare @.date1 datetime,
@.date2 datetime

Set @.date1 = '1/2/2006'
Set @.date2 = '1/19/2006'

Select next_end = DateAdd( dd, ( -1 * ( DatePart( dw, @.date1 ) - 1 ) + 7 ) % 7, @.date1 ),
last_end = DateAdd( dd, ( -1 * ( DatePart( dw, @.date2 ) - 1 ) ) , @.date2 )

The above will return the results you are expecting. You could place these into functions for better readability.

|||

Relying on the setting of @.@.DATEFIRST can be a problem, the best thing would be that @.@.DATEFIRST could be set to any day, bit still get the same result...

It's practical with a day-table containing each date, much like a numbers-table.

create table dbo.days (d datetime not null)
go
set nocount on
declare @.d datetime
set @.d = '20060101'
while @.d < '20070101'
begin
insert dbo.days select @.d
set @.d = dateadd(day, 1, @.d)
end
set nocount off
go

To get any given weekday from a date, regardless of @.@.DATEFIRST setting, we can use the following formula:
(( @.@.datefirst + datepart(weekday, @.mydate) -2 ) %7 + 1 )

Monday is 1, Tuesday 2 etc until Sunday that is 7.

So, if we want to know which dates that are Sundays between January 2nd 2006 and January 19th 2006, we can ask our date-table. (and not bother with what @.@.DATEFIRST may be set to)

declare @.startDate datetime, @.endDate datetime
select @.startDate = '20060102', @.endDate = '20060119'

select d from days
where d between @.startDate and @.endDate
and (( @.@.datefirst + datepart(weekday, d) -2 ) %7 + 1) = 7

The last '7' is the weekday (Sunday) we're looking for.

You can play with this example and see that nothing happens when @.@.datefirst changes.

set datefirst 3
select @.@.datefirst as 'datefirstSetTo'

declare @.startDate datetime, @.endDate datetime
select @.startDate = '20060102', @.endDate = '20060119'

select d as 'mySundays'
from days
where d between @.startDate and @.endDate
and (( @.@.datefirst + datepart(weekday, d) -2 ) %7 + 1) = 7

datefirstSetTo
--
3

(1 row(s) affected)

mySundays
2006-01-08 00:00:00.000
2006-01-15 00:00:00.000

(2 row(s) affected)

..hope it's of some help.

=;o)
/Kenneth

|||

Best is to use a calendar table that contains all the dates and query it to get the details. This approach gives maximum flexibility, maintainability and performance in lot of cases also. It is quite common to do this in data warehouses to simplify these type of queries. For example, you can build a Calendar table like:

create table Calendar (

dayid int not null primary key,

dt smalldatetime not null,

....

IsSaturday bit not null,

IsSunday bit not null

... other columns that can contain holidays or special days etc

)

Now your query becomes:

select count(*)

from Calendar

where dt between '20060102' and '20060119'

and 1 in ( IsSaturday, IsSunday )

You can answer more complex questions also using the calendar table which will be harder to do by writing UDFs or special procedural logic.