Weekly Reports Made Easy

Each week you are required to run various reports based on table entries made during the week preceding latest Sunday. Use this SQL7/2000 procedure (can be a UDF in SQL2K) to retrieve the latest midnight of a specified day of the week as illustrated below: Declare @StartDate datetime, @EndDate datetime Exec DBAservice.dbo.uspGetLatestWkDayMidnight @pDate = '1998-05-06', @pWeekDay ='Sunday', @pWDmidnight=@EndDate output Select @StartDate=dateadd(ww,-1,@EndDate) Select * from Northwind.dbo.orders where OrderDate>=@StartDate and OrderDate<@EndDate