Returning First Day of Week

Running SQL 2005 SP2.
I am wondering if there is a simple script or function that, for a given date,
I will get returned the previous Sunday at midnight?
For example, if my given date is today (Wednesday, February 6, 2008), my
return value would be 2/3/2008 00:00:00.000.
--
Message posted via http://www.sqlmonster.com"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7f58a15d04fbc@.uwe...
> Running SQL 2005 SP2.
> I am wondering if there is a simple script or function that, for a given
> date,
> I will get returned the previous Sunday at midnight?
> For example, if my given date is today (Wednesday, February 6, 2008), my
> return value would be 2/3/2008 00:00:00.000.
> --
> Message posted via http://www.sqlmonster.com
>
DECLARE @.dt DATETIME;
SET @.dt = CURRENT_TIMESTAMP;
SELECT DATEADD(DAY,7*FLOOR(DATEDIFF(DAY,'20000102',@.dt)/7.0),'20000102');
--
David Portas|||When I am working with dates I always stick in an auxillary calendar
table, this is very useful in quickly finding days of weeks etc.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html