Datum- und Zeitfunktionen

Aktualisiert:2. November 2023 Lesedauer: 8 Minuten SQL Beispiele 0
datum-zeit-funktion-sql
Auf den ersten Blick erscheinen nicht alle Funktionen als sinnvoll, insbesondere jene, die als kurze Einzeiler direkt in unsere Abfrage integriert werden könnten. Dennoch bevorzuge ich es, solche Funktionen zentral zu bündeln. Dies garantiert, dass wir konsistente Ergebnisse erhalten, egal ob die Funktion im ETL-Prozess oder direkt in einem Bericht verwendet wird.

Inhalt dieser Seite

Relevanz von Datum- und Zeitfunktionen im Data Warehouse

In der modernen Datenanalyse ist die Fähigkeit, zeitliche Muster und Trends zu erkennen, entscheidend. Das Data Warehousing, als zentrale Komponente der Business Intelligence, ermöglicht Unternehmen den Zugriff auf große Mengen historischer Daten. Hier spielen Datum- und Zeitfunktionen in SQL Server eine Schlüsselrolle. Sie erlauben es Datenanalysten und -ingenieuren, Zeitreihenanalysen durchzuführen, Datenintegrität zu gewährleisten und Berichte mit zeitlichen Dimensionen zu erstellen.

Zeitliche Dimension in der Datenanalyse

Die zeitliche Dimension ist oft das Rückgrat der Datenanalyse. Ob es darum geht, die monatlichen Verkaufszahlen zu überwachen, saisonale Schwankungen zu erkennen oder die Performance von Marketingkampagnen über die Zeit hinweg zu messen – ohne eine präzise und flexible Handhabung von Datum und Zeit wären solche Analysen undenkbar. SQL Server bietet hierfür eine Vielzahl von Funktionen, um Daten effektiv zu filtern, zu sortieren und zu aggregieren, basierend auf verschiedenen zeitlichen Kriterien.

Optimierung und Effizienz im Data Warehouse

In einem Data Warehouse ist die Performance essentiell. Eine effiziente Verarbeitung von Daten, insbesondere bei großen Datensätzen, erfordert spezialisierte Funktionen und Techniken. Die Datum- und Zeitfunktionen von SQL Server sind nicht nur leistungsstark, sondern auch optimiert für gängige Abfrageanforderungen in einem Data Warehouse-Umfeld. Durch ihre Verwendung können komplexe Abfragen beschleunigt, Datenkonsistenz sichergestellt und wertvolle Ressourcen gespart werden.

Erster und letzter Tag des Monats als Skalarwertfunktion:

CREATE FUNCTION FirstDayOfMonth(@Date DATE)
RETURNS DATE
AS
BEGIN
     RETURN DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)
END

Aufrufbeispiel:

SELECT dbo.FirstDayOfMonth('2023-10-31') AS FirstDay; -- Ergebnis: 2023-10-01

Funktion zur Ermittlung des letzten Tages eines Monats:

(benötigt die vorherige Funktion FirstDayOfMonth)

CREATE FUNCTION LastDayOfMonth(@Date DATE)
RETURNS DATE
AS
BEGIN
    RETURN DATEADD(DAY, -1, DATEADD(MONTH, 1, dbo.FirstDayOfMonth(@Date)))
END

Aufrufbeispiel:

SELECT dbo.LastDayOfMonth('2023-10-31') AS LastDay; -- Ergebnis: 2023-10-31

Funktion zur Umwandlung eines Datums in das ISO-Wochenformat:

CREATE FUNCTION IsoWeekOfYear(@Date DATE)
RETURNS INT
AS
BEGIN
      RETURN DATEPART(ISOWK, @Date)
END

Anwendungsbeispiel:

Angenommen, Sie haben eine Tabelle Bestellung mit einer Spalte Bestelldatum. Sie möchten den Gesamtumsatz pro ISO-Woche ermitteln (Für dieses Beispiel können Sie unser Test ERP nehmen, was Sie hier finden) :

SELECT 
   dbo.IsoWeekOfYear(Bestelldatum) AS WeekOfYear, 
   SUM(Preis) AS TotalPreis
FROM Bestellung left outer Join BestellDetail Detail ON Bestellung .BestellungID = Detail.BestellungID
WHERE YEAR(Bestelldatum)=2023
GROUP BY dbo.IsoWeekOfYear(Bestelldatum)
ORDER BY WeekOfYear;

Funktion zur Umwandlung von UTC in eine lokale Zeitzone:

CREATE FUNCTION ConvertUTCToLocal(@UTCDate DATETIME, @TimeZone NVARCHAR(50))
RETURNS DATETIME
AS
BEGIN
-- Dies ist eine vereinfachte Funktion. In der Praxis sollte ein vollständiges Mapping von Zeitzonenbezeichnungen zu ihren Offset-Werten vorhanden sein.
DECLARE @OffsetHours INT
IF @TimeZone = 'EST'
SET @OffsetHours = -5
ELSE IF @TimeZone = 'PST'
SET @OffsetHours = -8
-- ... Fügen Sie hier weitere Zeitzonen hinzu ...

RETURN DATEADD(HOUR, @OffsetHours, @UTCDate)
END

Aufrufbeispiel:

SELECT dbo.ConvertUTCToLocal('31.10.2023 15:00:00', 'EST') AS LocalTime; -- Ergebnis: 2023-10-31 10:00:00.000

Funktion zur Berechnung der Differenz zwischen zwei Daten in Arbeitstagen:

CREATE FUNCTION WorkingDaysDifference(@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
RETURN (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
- (DATEDIFF(WK, @StartDate, @EndDate) * 2)
- (CASE WHEN DATENAME(DW, @StartDate) = 'Sonntag' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(DW, @EndDate) = 'Samstag' THEN 1 ELSE 0 END)
END

Anwendungsbeispiel:

Angenommen, Sie haben eine Tabelle Bestellung mit den Spalten Bestelldatum und BeginnBearbeitungDatum. Sie möchten wissen, wie viele Arbeitstage jede Bestellung gedauert hat: Testdaten?=(Test ERP)

SELECT 
    [BestellungID], 
    dbo.WorkingDaysDifference(Bestelldatum, BeginnBearbeitungDatum) AS DurationInWorkingDays
FROM Bestellung 
WHERE BeginnBearbeitungDatum IS NOT NULL
ORDER BY DurationInWorkingDays DESC

Funktion zur Berechnung des Alters:

Diese Funktion berechnet das Alter basierend auf einem gegebenen Geburtsdatum:

CREATE FUNCTION CalculateAge(@BirthDate DATE)
RETURNS INT
AS
BEGIN
RETURN FLOOR(DATEDIFF(DAY, @BirthDate, GETDATE()) / 365.25)
END

Anwendungsbeispiel:

SELECT dbo.CalculateAge('29.08.1974') AS Age;
-- Ergebnis könnte beispielsweise "49" sein, abhängig vom aktuellen Datum

Funktion zur Rückgabe des Quartals für ein Datum:

Berechnet das Quartal eines gegebenen Datums:

CREATE FUNCTION GetQuarter(@Date DATE)
RETURNS INT
AS
BEGIN
RETURN DATEPART(QUARTER, @Date)
END

Anwendungsbeispiel:

SELECT dbo.GetQuarter('31.10.2023') AS Quarter; 
-- Ergebnis: "4", da Oktober zum 4. Quartal gehört

Funktion zur Umwandlung von UNIX-Zeitstempeln in SQL Server DateTime:

Viele Systeme und APIs verwenden UNIX-Zeitstempel. Mit dieser Funktion können Sie sie in ein SQL Server DateTime-Format umwandeln:

CREATE FUNCTION UnixTimestampToDateTime(@UnixTimestamp BIGINT)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(SECOND, @UnixTimestamp, '1970-01-01 00:00:00:000')
END

Anwendungsbeispiel:

SELECT dbo.UnixTimestampToDateTime(1699859200) AS DateTime; 
-- Ergebnis könnte beispielsweise "2023-11-13 07:06:40.000" sein

Funktion zur Ermittlung der Wochennummer eines Datums:

CREATE FUNCTION GetWeekNumberOfYear(@Date DATE)
RETURNS INT
AS
BEGIN
RETURN DATEPART(WEEK, @Date)
END

Aufrufbeispiel:

SELECT dbo.GetWeekNumberOfYear('31.10.2023') AS WeekNumber; 
-- Ergebins: 45

Funktion zur Ermittlung des Tagesnamens eines Datums:

CREATE FUNCTION GetDayName(@Date DATE)
RETURNS NVARCHAR(50)
AS
BEGIN
RETURN DATENAME(WEEKDAY, @Date)
END

Aufrufbeispiel:

SELECT dbo.GetDayName('31.10.2023') AS DayName; -- Ergebnis: "Dienstag" (wenn Ihr SQL Server auf Deutsch eingestellt ist)

Funktion zur Ermittlung des Unterschieds in Tagen zwischen zwei Daten:

CREATE FUNCTION DaysDifference(@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
RETURN DATEDIFF(DAY, @StartDate, @EndDate)
END

Aufrufbeispiel:

SELECT dbo.DaysDifference('31.10.2023', '24.12.2023') AS Difference; -- Ergebnis: 54

Funktion zur Ermittlung des ersten Arbeitstages eines Monats (unter der Annahme, dass Wochenenden keine Arbeitstage sind):

(benötigt die vorherige Funktion FirstDayOfMonth)

CREATE FUNCTION FirstWorkdayOfMonth(@Date DATE)
RETURNS DATE
AS
BEGIN
DECLARE @FirstDay DATE = dbo.FirstDayOfMonth(@Date)
WHILE DATENAME(WEEKDAY, @FirstDay) IN ('Samstag', 'Sonntag')
BEGIN
SET @FirstDay = DATEADD(DAY, 1, @FirstDay)
END
RETURN @FirstDay
END

Aufrufbeispiel:

SELECT dbo.FirstWorkdayOfMonth('31.10.2023') AS FirstWorkday; 
-- Ergebnis könnte beispielsweise 2023-10-02 sein, wenn es ein Arbeitstag ist.

Funktion zur Berechnung des letzten Arbeitstages des Monats:

(benötigt die vorherige Funktion LastDayOfMonth)

CREATE FUNCTION LastWorkdayOfMonth(@Date DATE)
RETURNS DATE
AS
BEGIN
DECLARE @LastDay DATE = dbo.LastDayOfMonth(@Date)
WHILE DATENAME(WEEKDAY, @LastDay) IN ('Samstag', 'Sonntag')
BEGIN
SET @LastDay = DATEADD(DAY, -1, @LastDay)
END
RETURN @LastDay
END

Aufrufbeispiel:

SELECT dbo.LastWorkdayOfMonth('15.08.2023') AS LastWorkday; -- 2023-08-31

Funktion zur Berechnung des ersten Tages eines Quartals:

CREATE FUNCTION FirstDayOfQuarter(@Date DATE)
RETURNS DATE
AS
BEGIN
DECLARE @Quarter INT = DATEPART(QUARTER, @Date)
RETURN DATEADD(QUARTER, @Quarter - 1, DATEFROMPARTS(YEAR(@Date), 1, 1))
END

Aufrufbeispiel:

SELECT dbo.FirstDayOfQuarter('2023-10-31') AS FirstDay; -- Ergebnis: 2023-10-01

Funktion zur Berechnung des letzten Tages eines Quartals:

CREATE FUNCTION LastDayOfQuarter(@Date DATE)
RETURNS DATE
AS
BEGIN
DECLARE @Quarter INT = DATEPART(QUARTER, @Date)
RETURN DATEADD(DAY, -1, DATEADD(QUARTER, @Quarter, DATEFROMPARTS(YEAR(@Date), 1, 1)))
END

Aufrufbeispiel:

SELECT dbo.LastDayOfQuarter('2023-10-31') AS LastDay; -- Ergebnis: 2023-12-31

Funktion zur Berechnung des Alters zu einem bestimmten Stichtag:

CREATE FUNCTION CalculateAgeOnDate(@BirthDate DATE, @OnDate DATE)
RETURNS INT
AS
BEGIN
RETURN CASE
WHEN MONTH(@BirthDate) > MONTH(@OnDate) OR (MONTH(@BirthDate) = MONTH(@OnDate) AND DAY(@BirthDate) > DAY(@OnDate)) THEN DATEDIFF(YEAR, @BirthDate, @OnDate) - 1
ELSE DATEDIFF(YEAR, @BirthDate, @OnDate)
END
END

Aufrufbeispiel:

SELECT dbo.CalculateAgeOnDate('29.08.1974', '05.06.2024') AS Age; -- Ergebnis: 49

Funktion zur Überprüfung, ob ein Jahr ein Schaltjahr ist:

CREATE FUNCTION IsLeapYear(@Year INT)
RETURNS BIT
AS
BEGIN
RETURN CASE
WHEN (@Year % 4 = 0 AND @Year % 100 <> 0) OR @Year % 400 = 0 THEN 1
ELSE 0
END
END

Aufrufbeispiel:

SELECT dbo.IsLeapYear(2024) AS IsLeap; -- Ergebnis:1 (True)

Funktion zur Berechnung der Anzahl der Arbeitstage zwischen zwei Daten:

CREATE FUNCTION WorkdaysBetweenDates(@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
DECLARE @TotalDays INT = DATEDIFF(DAY, @StartDate, @EndDate) + 1
DECLARE @Weekends INT = (DATEDIFF(WEEK, @StartDate, @EndDate) * 2) +
CASE WHEN DATENAME(WEEKDAY, @StartDate) = 'Sonntag' THEN 1 ELSE 0 END +
CASE WHEN DATENAME(WEEKDAY, @EndDate) = 'Samstag' THEN 1 ELSE 0 END
RETURN @TotalDays - @Weekends
END

Aufrufbeispiel:

SELECT dbo.WorkdaysBetweenDates('27.10.2023', '01.11.2023') AS Workdays; -- Ergebnis: 4

Funktion zur Rückgabe des nächsten spezifischen Wochentags:

Diese Funktion gibt das Datum des nächsten spezifizierten Wochentags zurück. Zum Beispiel kann es verwendet werden, um das Datum des nächsten Montags zu finden.

CREATE FUNCTION NextSpecificWeekday(@Date DATE, @WeekdayName NVARCHAR(50))
RETURNS DATE
AS
BEGIN
DECLARE @NextDate DATE = @Date
WHILE DATENAME(WEEKDAY, @NextDate) <> @WeekdayName
BEGIN
SET @NextDate = DATEADD(DAY, 1, @NextDate)
END
RETURN @NextDate
END

Aufrufbeispiel:

SELECT dbo.NextSpecificWeekday('31.10.2023', 'Montag') AS NextDate; -- Ergebnis 2023-11-06.

Funktion zur Berechnung der Dauer in Tagen, Stunden, Minuten und Sekunden:

CREATE FUNCTION ElapsedTime(@StartDate DATETIME, @EndDate DATETIME)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @Duration INT = DATEDIFF(SECOND, @StartDate, @EndDate)
DECLARE @Days INT = @Duration / (3600 * 24)
SET @Duration = @Duration % (3600 * 24)
DECLARE @Hours INT = @Duration / 3600
SET @Duration = @Duration % 3600
DECLARE @Minutes INT = @Duration / 60
DECLARE @Seconds INT = @Duration % 60
RETURN CONCAT(@Days, 'd ', @Hours, 'h ', @Minutes, 'm ', @Seconds, 's')
END

Aufrufbeispiel:

SELECT dbo.ElapsedTime('01.11.2023 08:00:00', '04.11.2023 10:15:30') AS Elapsed; -- Ergebnis: "3d 2h 15m 30s"

Funktion zur Überprüfung, ob ein Datum ein Feiertag ist:

Hier ein einfaches Beispiel, das einige Feiertage berücksichtigt. Sie könnten eine umfangreichere Liste von Feiertagen oder eine externe Tabelle mit Feiertagsdaten verwenden.


CREATE FUNCTION IsHoliday(@Date DATE)
RETURNS BIT
AS
BEGIN
   DECLARE @Result BIT

    IF @Date IN ('01.01.2024','29.03.2024','01.04.2024','01.05.2024','09.05.2024','20.05.2024','03.10.2024','31.10.2024','25.12.2024','26.12.2024', '01.01.2025','18.04.2025','21.04.2025','01.05.2025','29.05.2025','09.06.2025','03.10.2025','31.10.2025','25.12.2025','26.12.2025')  -- Neujahr, Weihnachten, Zweiter Weihnachtsfeiertag
        BEGIN
        SET @Result = 1
    END
    ELSE
    BEGIN
        SET @Result = 0
    END

    RETURN @Result
END

Aufrufbeispiel:

SELECT dbo.IsHoliday('31.10.2024') AS IsHoliday; -- Ergebnis: 1 (True)

Funktion zur Ermittlung des Datums des letzten spezifischen Wochentags im Monat:

Zum Beispiel kann diese Funktion verwendet werden, um das Datum des letzten Freitags im Monat zu finden.

CREATE FUNCTION LastSpecificWeekdayOfMonth(@Date DATE, @WeekdayName NVARCHAR(50))
RETURNS DATE
AS
BEGIN
    DECLARE @LastDayOfMonth DATE
    SET @LastDayOfMonth = EOMONTH(@Date)  -- EOMONTH gibt das Datum des letzten Tages des Monats zurück

    -- Solange der Wochentag des @LastDayOfMonth nicht dem gewünschten Wochentag entspricht,
    -- wird ein Tag abgezogen, bis der gewünschte Wochentag erreicht ist.
    WHILE DATENAME(WEEKDAY, @LastDayOfMonth) <> @WeekdayName
    BEGIN
        SET @LastDayOfMonth = DATEADD(DAY, -1, @LastDayOfMonth)
    END

    RETURN @LastDayOfMonth
END

Aufrufbeispiel:

SELECT dbo.LastSpecificWeekdayOfMonth('01.11.2023', 'Freitag') AS LastFriday; --2023-11-24

Funktion zur Ermittlung des Tages im Jahr:

CREATE FUNCTION DayOfYear(@Date DATE)
RETURNS INT
AS
BEGIN
RETURN DATEPART(DAYOFYEAR, @Date)
END

Aufrufbeispiel:

SELECT dbo.DayOfYear('01.11.2023') AS DayNumber; -- Ergebnis könnte beispielsweise 305 sein.

Funktion zur Ermittlung der Anzahl der Tage in einem Monat:

CREATE FUNCTION DaysInMonth(@Date DATE)
RETURNS INT
AS
BEGIN
RETURN DAY(DATEADD(DAY, -1, DATEADD(MONTH, 1, dbo.FirstDayOfMonth(@Date))))
END

Aufrufbeispiel:

SELECT dbo.DaysInMonth('01.11.2023') AS DaysCount; -- Ergebnis: 30

Funktion zur Ermittlung der Anzahl der Wochen in einem Monat:

Hierbei gehen wir von einer Woche aus, die von Montag bis Sonntag geht.

CREATE FUNCTION WeeksInMonth(@Date DATE)
RETURNS INT
AS
BEGIN
DECLARE @FirstDayOfMonth DATE = dbo.FirstDayOfMonth(@Date)
DECLARE @LastDayOfMonth DATE = dbo.LastDayOfMonth(@Date)
DECLARE @FirstSundayOfMonth DATE = dbo.NextSpecificWeekday(@FirstDayOfMonth, 'Sonntag')
RETURN (DATEDIFF(DAY, @FirstSundayOfMonth, @LastDayOfMonth) / 7) + 1
END

Aufrufbeispiel:

SELECT dbo.WeeksInMonth('01.10.2023') AS WeeksCount; -- Das Ergebnis 5

Ergänzend möchte ich an dieser Stelle T-SQL-Abfragen zur Ermittlung verschiedener Datums- und Zeitgrenzen vorstellen

Erster und letzter Tag der Woche für ein beliebiges Datum (nicht nur basierend auf dem aktuellen Datum):

-- Erster Tag der Woche (Montag) für ein bestimmtes Datum
SELECT CAST(DATEADD(DAY, 1 - DATEPART(WEEKDAY, @YourDate), @YourDate) AS DATE) AS start_of_week_for_date

-- Letzter Tag der Woche (Sonntag) für ein bestimmtes Datum
SELECT CAST(DATEADD(DAY, 7 - DATEPART(WEEKDAY, @YourDate), @YourDate) AS DATE) AS end_of_week_for_date

Quartalsanfang und -ende für das aktuelle Quartal:

-- Start of current quarter
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CURRENT_TIMESTAMP), 0) AS start_of_current_quarter

-- End of current quarter
SELECT DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CURRENT_TIMESTAMP) + 1, 0)) AS end_of_current_quarter

Die Anzahl der Arbeitstage (Montag bis Freitag) im aktuellen Monat:

-- Number of weekdays in the current month
SELECT COUNT(*) AS weekdays_in_current_month
FROM (
    SELECT TOP (DAY(EOMONTH(CURRENT_TIMESTAMP))) 
           DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), MONTH(CURRENT_TIMESTAMP), 1)) AS DayDate
    FROM sys.objects
) AS MonthDays
WHERE DATEPART(WEEKDAY, DayDate) BETWEEN 2 AND 6 -- assuming 1 = Sunday, 7 = Saturday

Das aktuelle Finanzjahr ermitteln (angenommen, das Finanzjahr beginnt am 1. April):

-- Start of current financial year
SELECT CASE 
           WHEN MONTH(CURRENT_TIMESTAMP) < 4 
           THEN DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP) - 1, 4, 1) 
           ELSE DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 4, 1) 
       END AS start_of_current_financial_year

-- End of current financial year
SELECT CASE 
           WHEN MONTH(CURRENT_TIMESTAMP) < 4 
           THEN DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 3, 31) 
           ELSE DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP) + 1, 3, 31) 
       END AS end_of_current_financial_year

Wie hat dir der Artikel gefallen?

Vielen Dank für dein Feedback!

Ähnliche Beiträge:

Datenqualitätsfunktionen – Optimierung der Datenqualität im DWH Bytes als FunktionUmwandlung von Bytes als Funktion XML – Das Extensible Markup Language SplitString als Funktion anlegenSplitString als Funktion anlegen SQL Server Sequenzen: Nutzung und Beispiele ETL Überwachung sowie Datenintegrität und DatenqualitätSQL Beispiele für die ETL Überwachung sowie Datenintegrität und Datenqualität
Über Frank 74 Artikel

Ich bin Frank, Data Warehouse und BI-Entwickler mit langjähriger Expertise in diesem Bereich. Ich verfüge über mehr als 20 Jahre Berufserfahrung im DWH Umfeld. Das Analysieren und Interpretieren von Zahlen, Daten und Fakten ist meine große Leidenschaft, aus diesem Grunde ist auch diese Seite hier entstanden.

Webseite Facebook Xing

Hinterlasse jetzt einen Kommentar

Kommentar hinterlassen

E-Mail Adresse wird nicht veröffentlicht.


*