Hello there, Actually i was facing problem last year to get the exact week no. of the year. i found the following code which will let us know the exact week no. of the year. so, i thought to put an article on my site. It will help to other also. just copy and paste the following script into your database to get the isoweek / week number.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION [dbo].[ISOweek] (@DATE DATETIME)
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
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
How to execute see the following example..
select getdate() as currentdate
select DBO.ISOWEEK(getdate()) as weekno