/fdata%2F2470622%2Favatar-blog-1074652166-tmpphpjYbJuw.jpg){kind=avatar}
Datum in ISO Kalenderwoche (2)
Noch einmal das Thema „Datum in ISO Kalenderwoche“ für den MS SQL Server, dieses Mal ein T-SQL Script als „Table Value Function“ ausgelegt. Verwendet kann es dann als Tabelle mittels
CROSS APPLY
dbo.fnDate2ISOYearWeekTable(SRC.DATUM) AS ISO
Hier das Script für die Funktion:
-- Alte Version ggf. DROPen IF OBJECT_ID (N'dbo.fnDate2ISOYearWeekTable', N'TF') IS NOT NULL
DROP FUNCTION dbo.fnDate2ISOYearWeekTable; GO CREATE FUNCTION dbo.fnDate2ISOYearWeekTable(@Date datetime)
RETURNS @Result TABLE (ISOYear int NOT NULL,
ISOWeek smallint NOT NULL) WITH EXECUTE AS CALLER
AS -- fnDate2ISOYearWeekTable - Stand 23.03.2009 -- Liefert zu einem Datum die Kalenderwoche und -Jahr -- als Tabelle BEGIN DECLARE @ISOweek smallint, @ISOYear int; SET @ISOYear = DATEPART(yy, @Date); SET @ISOweek = DATEPART(wk, @DATE) + 1 - DATEPART(wk, CAST(@ISOYear as CHAR(4)) + '0104'); -- Sonderfall: Erste KW <> 4 Tage
IF (@ISOweek = 0) BEGIN
SET @ISOYear = @ISOYear - 1; SET @ISOweek = DATEPART(wk, CAST(@ISOYear AS CHAR(4)) + '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2)) ) + 1 - DATEPART(wk, CAST(@ISOYear as CHAR(4)) + '0104') + 1;
END; --Sonderfall: Letzte 3 Tage falen in erste KW Folgejahr IF DATEPART(mm, @DATE) = 12
AND DATEPART(dd, @DATE) - DATEPART(dw, @DATE) >= 28 BEGIN SET @ISOweek = 1; SET @ISOYear = @ISOYear + 1; END; INSERT INTO @Result VALUES (@ISOYear, @ISOWeek); RETURN; END;
GO -- Unit-Test mit Sonder- und Normalfällen SELECT Datum, DATENAME(dw, Datum) AS WochenTag,
DATENAME(isowk, Datum) AS InternalIsoWeek,
dbo.ISOweek(Datum) AS MSIsoWeek, ISO.ISOYear, ISO.ISOWeek FROM (SELECT CAST('20060101' as datetime) AS DATUM UNION
SELECT CAST('20060102' as datetime) AS DATUM UNION SELECT CAST('20061231' as datetime) AS DATUM UNION SELECT CAST('20050101' as datetime) AS DATUM UNION SELECT CAST('20050102' as datetime) AS DATUM UNION SELECT CAST('20050103' as datetime) AS DATUM ) AS SRC CROSS APPLY dbo.fnDate2ISOYearWeekTable(SRC.DATUM) AS ISO ORDER BY DATUM
Werbung
Diesen Post teilen
Um über die neuesten Artikel informiert zu werden, abonnieren:
/idata%2F2470622%2FDESCRIBE_SC1.jpg)
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
Kommentiere diesen Post
