Not sure which versions of SQL Server this works in (but it works in 2005).

declare @dt_date datetime;

declare @dt_date_prev datetime;

set @dt_date = convert(datetime,'2009-09-01');

set @dt_date_prev = @dt_date-1;

I think Lynn's solution is best using

SELECT 1 + DATEDIFF(DAY, 0, @target_date) % 7

but it will fail for dates prior to 1/1/1900 - not a problem in most scenarios

but...