Sometimes it is necessary for reporting purposes to query data for a caldendar week.  When doing so, us the the datepart function to retrieve the weeknum will not work because if a week spans more than one year it will only return the data for the same year.  For example, January 1, 2009 is a Thursday.  So the only dates that are in Week one of 2009 are January 1-3 (Thursday-Saturday).  So how would you include from Sunday forward?  The below function will help you.

This function will return, for a given @InputDate, the Sunday directly preceeding it.  If the supplied @InputDate is a Sunday, it will return itself.  This function also returns only the DATE portion, stripping the time.

Here's the function as well as a couple of sample calls.

Oops, We've found a bug.  We are temporarily removing this function while we fix it.

There you have it.  Now if you need to run a query against all data from a table where the created_dttm is in the week of January 1, 2009.

declare @Sunday datetime
set @Sunday = dbo.fn_SundayOfWeek ('2009-01-01')

select * from SomeTable
where created_dttm >= @Sunday 
  and created_Dttm < @Sunday-7

The above query says to return all records whose created_dttm is between the Sunday before and the Saturday after, January 1, 2009.

I hope you found this useful.  Check back soon to see what other useful utility scripts we may have provided.