datetime to ISOweek & ISOyear

Hi,

I need a UDF (let's call it ISOyyyyww)  which requires a datetime input and returns a varchar(6) consisting of the ISOyear(as yyyy) concatenated with the ISOweek (as ww) according to the ISO 8601 standard. In BOL is an example for the ISOweek, but that doesn't include the year.

example

select dbo.ISOyyyyww('20031229')
should return : '200401'

select dbo.ISOyyyyww('20040125')
should return: '200404'

select dbo.ISOyyyyww('20000101')
should return: '199952'

I have made the function myself a while back, but it's monday morning and I can't remember where I put it and I don't want to make it again ;-)

If the solution works with every @@DATEFIRST setting it would be great!
LVL 9
auke_tAsked:
Who is Participating?
 
CetusMODConnect With a Mentor Commented:
Closed, 125 points refunded.
CetusMOD
Community Support Moderator
0
 
Dishan FernandoSoftware Engineer / DBACommented:
0
 
auke_tAuthor Commented:
--Ok, just did it myself the easy way.

--This is 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

/*
This is a function that abuses the fact that a weeknr of 1 in December has ISOyear = realyear + 1 and a weeknr > 50 in January has ISOyear = realyear - 1
*/
CREATE FUNCTION ISOyyyyww (@DATE datetime)
RETURNS varchar(6)
AS
BEGIN
      DECLARE @ISOyear int
      DECLARE @ISOweek int

      DECLARE @YEAR int
      DECLARE @MONTH int

      SET @ISOweek = dbo.ISOweek(@date)

      SET @YEAR = DATEPART(year, @date)
      SET @MONTH = DATEPART(month, @date)

      SET @ISOyear = @year
      IF @MONTH = 1 and @ISOWEEK > 50 SET @ISOyear = @YEAR - 1
      IF @MONTH = 12 and @ISOWEEK = 1 SET @ISOyear = @YEAR + 1
      
      RETURN convert(varchar(4), @ISOyear) +  right('0' + convert(varchar(2), @ISOweek), 2)
END
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.