• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1509
  • Last Modified:

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!
0
auke_t
Asked:
auke_t
1 Solution
 
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
 
CetusMODCommented:
Closed, 125 points refunded.
CetusMOD
Community Support Moderator
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now