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

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