Technical Article

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

Create proc uspGetLatestWkDayMidnight 
@pDate datetime = NULL,
@pWeekDay varchar(9)='Sunday',
@pWDmidnight datetime=null Output

Purpose: Returns midnight of a specified week day @pWeekDay preceding given date @pDate.
Developed: Yul Wasserman (; 03/12/03
Set Nocount ON
If @pWeekDay not in ('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')
Raiserror('Not a valid week day',16,1)
Return -1
Declare @Dt datetime
Select @Dt=ISNULL(@pDate,getdate())
Select @Dt= Convert(datetime,rtrim(Cast(MONTH(@Dt)as Char))+'/'  
+rtrim(Cast(DAY (@Dt)as Char))+'/'  
+rtrim(Cast(year (@Dt)as Char))+' 00:00:00.000')    

WHILE DATENAME(dw,@dt)<>@pWeekDay Select @dt=dateadd(dd,-1,@dt)

Select @pWDmidnight=@Dt
--Select @pWDmidnight
Set Nocount OFF
Return DATEPART (dw , @pWDmidnight)