Incorrect week number with DATENAME in localized query

Hi,

I'm trying to use the DATENAME function to get a correct Dutch week number, but the DATENAME function seems not to return a localized week number. This is how I have tested it:

-- Set language to English
SET LANGUAGE us_English

-- Declare to dates 12/30/2006 and 12/31/2006
DECLARE @.Dec30 AS DATETIME SET @.Dec30 = CONVERT(DateTime, '2006-12-30')
DECLARE @.Dec31 AS DATETIME SET @.Dec31 = CONVERT(DateTime, '2006-12-31')

-- Return information about the declared dates in English
SELECT @.Dec30 as date1, DATENAME(week, @.Dec30) as week1, DATENAME(weekday, @.Dec30) as day1,
@.Dec31 as date2, DATENAME(week, @.Dec31) as week2, DATENAME(weekday, @.Dec31) as day2

-- Set language to Dutch
SET LANGUAGE Dutch

-- Return information about the declared dates in Dutch
SELECT @.Dec30 as date1, DATENAME(week, @.Dec30) as week1, DATENAME(weekday, @.Dec30) as day1,
@.Dec31 as date2, DATENAME(week, @.Dec31) as week2, DATENAME(weekday, @.Dec31) as day2

In both the English and Dutch results Saturday (12/30/2006) has week number 52 and Sunday (12/31/2006) has week number 53, but this is incorrect for the Dutch language. Sunday should also have week number 52, because the week starts on Monday in The Netherlands.

What am I doing wrong here and how can I get the correct localized week numbers from SQL Server? (I'm using SQL Server 2000 + SP4)

Thanks in advance.

SET Language is used configure the following options, DateFormat, DateFirst, Names of the Month & Names of the Days. So your in the rite direction to get your result..

BUT,

Unfortuantlly SQL Server won't help you to get the proper Week Number. Bcs they are not following ISO standard as you think. The Week 1 always = 1 - jan -any year. So sometimes you will get wrong week number.

The best approach to get the week number is use the custom function to get the week number.

Create Function dbo.MyWeekNo(@.dateFirst int, @.DateValue as DateTime) Returns Int
As
Begin
Declare @.Date as Datetime
declare @.Date2 as Datetime
Declare @.Week as int
Select @.Date = Convert(Varchar,Year(@.DateValue)) + '-01-01', @.Date2=DateAdd(DD,-1,@.Date)

Select @.Week = Case When WeekNo=0 Then dbo.MyWeekNo(@.dateFirst,@.Date2) Else WeekNo End
From
(
Select
Case When DatePart(W,@.Date) >= @.dateFirst Then DatePart(WW,@.DateValue) -1
Else DatePart(WW,@.DateValue) End WeekNo
) as Weeks

Return @.Week;
End

|||

Hi ManiD,

Your function is very close to the function I'm using for years now:

CREATE FUNCTION dbo.DutchWeek(@.DATE AS DateTime) RETURNS Int AS
BEGIN
IF @.DATE IS NULL RETURN NULL;

DECLARE @.JANFIRST AS DateTime
DECLARE @.WEEKDAY AS Int
DECLARE @.DAY AS Int
DECLARE @.DAYOFYEAR AS Int
DECLARE @.WEEKNUMBER AS Int

-- Get Januari the first of the year of @.DATE
SET @.JANFIRST = CONVERT(datetime, '1/1/' + CONVERT(varchar, YEAR(@.DATE)))

-- Calculate the number of the day where 0 = Monday, 1 = Tuesday, etc...
SET @.WEEKDAY = CONVERT(Int, @.JANFIRST) % 7

-- Calculate the (zero-bases) day number (0..265)
SET @.DAYOFYEAR = CONVERT(integer, @.DATE - @.JANFIRST)
-- Calculate the dutch week number
SET @.WEEKNUMBER = (@.DAYOFYEAR + @.WEEKDAY) / 7 +
CASE WHEN @.WEEKDAY > 3 THEN 0 ELSE 1 END

-- When week number is 0, get the weeknumber of the last week of the
-- previous year
IF @.WEEKNUMBER = 0
SET @.WEEKNUMBER = dbo.DutchWeek('12/31/' +
CONVERT(varchar, YEAR(@.DATE)-1));
RETURN @.WEEKNUMBER;
END

I use this function for years, but always had the feeling SQL Server should do this for me. But this is not the case, is it?