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!
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!
Dishan Fernando
http://www.thecodeproject.com/csharp/GregToISO.asp
ASKER
--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
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEP
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
--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
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEP
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.