Written by 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
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[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
GO
——————————————————————————————————–
How to execute see the following example..
select getdate() as currentdate
select DBO.ISOWEEK(getdate()) as weekno
with Regards
vik
Recent Comments