Solved

datetime to ISOweek & ISOyear

Posted on 2004-08-30
4
1,452 Views
Last Modified: 2007-12-19
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
Comment
Question by:auke_t
4 Comments
 
LVL 8

Expert Comment

by:dishanf
ID: 11929634
0
 
LVL 9

Author Comment

by:auke_t
ID: 12014942
--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
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 12056692
Closed, 125 points refunded.
CetusMOD
Community Support Moderator
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question