query to return week number 1 for first week of year starting on a monday?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob - 30 Mar 2008 12:01 GMT

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

Reply to this Message

Uri Dimant - 30 Mar 2008 12:33 GMT

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

> Hi,
>
[quoted text clipped - 13 lines]
>
> Thanks

Reply to this Message