Pull Date for Last Thursday

Hi,

I am having a problem with a report. An extract runs every Thursday and on the last day of the month. I need the report to run on-demand for the most recent Thursday (so, if they run it on Fri, Sat or Sun, they get last Thursday's value).

I can't figure out how to pull data where the completedate = last Thursday's date.

Thanks,

RC

I use a function similar to this:

Code Snippet

CREATE FUNCTION dbo.LastThursday
( @.DateIn datetime,
@.WithTime bit = 0 -- 1=Yes, 0=No
)
RETURNS datetime
AS
BEGIN
DECLARE
@.DateOut datetime,
@.NumDays int

SELECT @.NumDays = CASE datepart( dw, @.DateIn )
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
WHEN 4 THEN 6
WHEN 5 THEN 7
WHEN 6 THEN 1
WHEN 7 THEN 2
END

IF ( @.WithTime = 1 )
SET @.DateOut = dateadd( day, -@.NumDays, @.DateIn )
ELSE
SET @.DateOut = cast( convert( char(10), dateadd( day, -@.NumDays, @.DateIn ), 101 ) AS datetime )

RETURN @.DateOut

END
GO

Usage:

Code Snippet

SELECT dbo.LastThursday( getdate(), 0 )
-
2007-06-07 00:00:00.000

In your Query WHERE clause:

WHERE CompleteDate = dbo.LastThursday( getdate(), 0 )

If you use this on Thursday, this week, it will return Thursday LAST week. If you want it to return Thursday, this week, change the "WHEN 5 THEN 7" to "THEN 0"