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.
SQL Lernen
CREATE FUNCTION FirstDayOfMonth(@Date DATE)
RETURNS DATE
AS
BEGIN
RETURN DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)
ENDAufrufbeispiel:
SELECT dbo.FirstDayOfMonth('2023-10-31') AS FirstDay; -- Ergebnis: 2023-10-01Funktion 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)))
ENDAufrufbeispiel:
SELECT dbo.LastDayOfMonth('2023-10-31') AS LastDay; -- Ergebnis: 2023-10-31Funktion zur Umwandlung eines Datums in das ISO-Wochenformat:
CREATE FUNCTION IsoWeekOfYear(@Date DATE)
RETURNS INT
AS
BEGIN
RETURN DATEPART(ISOWK, @Date)
ENDAnwendungsbeispiel:
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)
ENDAufrufbeispiel:
SELECT dbo.ConvertUTCToLocal('31.10.2023 15:00:00', 'EST') AS LocalTime; -- Ergebnis: 2023-10-31 10:00:00.000Funktion 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)
ENDAnwendungsbeispiel:
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 DESCFunktion 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)
ENDAnwendungsbeispiel:
SELECT dbo.CalculateAge('29.08.1974') AS Age;
-- Ergebnis könnte beispielsweise "49" sein, abhängig vom aktuellen DatumFunktion 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)
ENDAnwendungsbeispiel:
SELECT dbo.GetQuarter('31.10.2023') AS Quarter;
-- Ergebnis: "4", da Oktober zum 4. Quartal gehörtFunktion 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')
ENDAnwendungsbeispiel:
SELECT dbo.UnixTimestampToDateTime(1699859200) AS DateTime;
-- Ergebnis könnte beispielsweise "2023-11-13 07:06:40.000" seinFunktion zur Ermittlung der Wochennummer eines Datums:
CREATE FUNCTION GetWeekNumberOfYear(@Date DATE)
RETURNS INT
AS
BEGIN
RETURN DATEPART(WEEK, @Date)
ENDAufrufbeispiel:
SELECT dbo.GetWeekNumberOfYear('31.10.2023') AS WeekNumber;
-- Ergebins: 45Funktion zur Ermittlung des Tagesnamens eines Datums:
CREATE FUNCTION GetDayName(@Date DATE)
RETURNS NVARCHAR(50)
AS
BEGIN
RETURN DATENAME(WEEKDAY, @Date)
ENDAufrufbeispiel:
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)
ENDAufrufbeispiel:
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
ENDAufrufbeispiel:
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
ENDAufrufbeispiel:
SELECT dbo.LastWorkdayOfMonth('15.08.2023') AS LastWorkday; -- 2023-08-31Funktion 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))
ENDAufrufbeispiel:
SELECT dbo.FirstDayOfQuarter('2023-10-31') AS FirstDay; -- Ergebnis: 2023-10-01Funktion 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)))
ENDAufrufbeispiel:
SELECT dbo.LastDayOfQuarter('2023-10-31') AS LastDay; -- Ergebnis: 2023-12-31Funktion 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
ENDAufrufbeispiel:
SELECT dbo.CalculateAgeOnDate('29.08.1974', '05.06.2024') AS Age; -- Ergebnis: 49Funktion 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
ENDAufrufbeispiel:
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
ENDAufrufbeispiel:
SELECT dbo.WorkdaysBetweenDates('27.10.2023', '01.11.2023') AS Workdays; -- Ergebnis: 4Funktion 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
ENDAufrufbeispiel:
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')
ENDAufrufbeispiel:
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
ENDAufrufbeispiel:
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-24Funktion zur Ermittlung des Tages im Jahr:
CREATE FUNCTION DayOfYear(@Date DATE)
RETURNS INT
AS
BEGIN
RETURN DATEPART(DAYOFYEAR, @Date)
ENDAufrufbeispiel:
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))))
ENDAufrufbeispiel:
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
ENDAufrufbeispiel:
SELECT dbo.WeeksInMonth('01.10.2023') AS WeeksCount; -- Das Ergebnis 5Ergä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?
Ähnliche Beiträge:
Mögliche Zwei-Faktor-Authentifizierung (2FA) beim Microsoft SQL Server 2022
SplitString als Funktion anlegen
Erweiterung der Rendering-Methoden in SSRS
SQL Server und JSON: Laden, Analysieren und Verstehen von JSON-Daten
Umwandlung von Bytes als Funktion
Vollständiges, inkrementelles und Delta-Laden im ETL-Prozess
Be the first to comment