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
Hinterlasse jetzt einen Kommentar