Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

Datum in ISO Kalenderwoche (2)

29. März 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

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  

Diesen Post teilen

Repost 0

Kommentiere diesen Post