November 20, 2002 at 2:02 am
I have a problem with the datepart function.
I want to use datepart to get the week number but for years where January 1 is on a friday, saturday or sunday I don´t get the correct answer.
Ex. select datepart (week, '2005-01-01') returns 1, but according to my calendar it should be 53.
(the ISO 8601 standard where the first week of the year is guaranteed to have a minimum of 4 days)
Datefirst is set to 1.
What can I do to be sure that I always get the correct week number?
SQL Server 2000 sp 2
November 20, 2002 at 7:17 am
Maybe something like this is what you are looking for.
SELECT
CASE
WHEN
(DATEPART(wk, 'PutDateHere(Ex.2/14/2005)') - (CASE WHEN DATEPART(dw, 'PutJan1ForThatYearDateHere(Ex.1/1/2005)') > 3 THEN 1 ELSE 0 END)) = 0 THEN 53
ELSE
DATEPART(wk, 'PutDateHere(Ex.2/14/2005)') - (CASE WHEN DATEPART(dw, 'PutJan1ForThatYearDateHere(Ex.1/1/2005)') > 3 THEN 1 ELSE 0 END)
END
You can even work this into your of User Defined Function in SQL 2000 to make is reusable without so much coding.
November 20, 2002 at 8:14 am
Here is a function to ISO Week right from BOL:
CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply