Weekly Reports Made Easy

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 (yul@ugo.com; if_then02@yahoo.com) 03/12/03
******/AS
Set Nocount ON
If @pWeekDay not in ('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')
Begin
Raiserror('Not a valid week day',16,1)
Return -1
End
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) 
/*uspGetLatestWkDayMidnight*/GO