Rob
DECLARE @today AS DATETIME;
SET @today = CAST(CONVERT(CHAR(8), '20070101', 112) AS DATETIME);

SELECT
@today AS today,
DATENAME(Week,@today - DATEPART(weekday, @today + @@DATEFIRST - 1) + 1)
AS week_start_date



"Rob" <r_miller@ozemail.com.au> wrote in message
news:uxq8GXlkIHA.748@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> I'm trying to put together a query that will return number 1 for the first
> week of the year that starts on a monday. I've found the following query:
>
> select datepart(week, DateAdd(day, -1 * DATEPART (dw, '06-JAN-2008') -1,
> '06-JAN-2008' ))
>
> This will successfully return 53 for '06-JAN-2008' and 1 for '07-JAN-2008'
> However, it will return 53 for '01-JAN-2007' when it should be returning
> 1. So basically, it's inconsistent.
> Does anyone know of a query that can correctly return the week number
> given that the first week of the year begins on the first monday of the
> year. Also, it would be good if the query was able to adapt to a database
> who's first date is set to Sunday, or Monday
>
> Thanks