Wer einmal eine CSV-Spalte mit gemischten Datums-Formaten in eine datetime-Spalte importieren musste, weiß: Datenqualität beginnt bei der Typ-Konvertierung. SQL Server lässt einen mit style-Codes alleine, sobald das Format vom Standard abweicht — TRY_CONVERT deckt die dokumentierten Formate ab, alles andere braucht eine eigene Funktion.
Das nimmst du mit:
- wann
TRY_CONVERTmitstyle-Code ausreicht — und wann eine eigene Funktion her muss; - die vollständige Tabelle der unterstützten
style-Codes fürdate/datetime/datetime2/time; - vier produktive Funktionen
fnConvertDate,fnConvertTime,fnConvertDateTime,fnConvertDateTime2mit Format-String- statt Style-Code-Schnittstelle; - die Postgres-Brücke (
TO_DATE,TO_TIMESTAMP) als 1:1-Pendant für den Multi-Engine-ETL-Alltag; - ein FAQ-Block mit den häufigsten Stolperfallen aus dem CSV-/JSON-Import.
Voraussetzung: SQL Server 2017+ (für TRY_CONVERT-Styles 23/126), für die Postgres-Brücke PostgreSQL 12+.
Inhalt
- Überblick
- style-Codes
- Formatbezeichner
- Benutzerdefinierte Funktionen
- Postgres-Brücke
- Zusammenfassung
- FAQ
- Verwandte Artikel
Überblick
Die wohl herausforderndste Konvertierung ist die Konvertierung eines Datums und/oder Uhrzeit, das oder die als Text geliefert wird, in einen Wert vom Typ date, time, datetime oder datetime2.
SQL Server stellt für die Speicherung eines Datums und/oder einer Uhrzeit unter anderem die folgenden Datentypen zur Verfügung:
- date
- time
- datetime
- datetime2
Für die Konvertierung eines als Text gelieferten Datums stehen die Funktionen CONVERT und TRY_CONVERT als Standardfunktionen bereit, die über den Parameter style das jeweilige Datumsformat des gelieferten Datums identifizieren. So gibt zum Beispiel der style-Parameter 104 an, dass das zu konvertierende Datum in dem Parameter expression ein Deutsches Datum mit vierstelliger Jahreszahl gemäß der Formatierungszeichenfolge dd.mm.yyyy ist.
1: TRY_CONVERT(date, ’24.04.2023’, 104) -- 24.04.2023
Die unterstützten Datumsformate sind in der Online-Dokumentation unter CAST and CONVERT (Transact-SQL) beschrieben. Jedem Datumsformat ist ein style-Code zugewiesen, der als dritter Parameter an die Funktion TRY_CONVERT aber auch CONVERT übergeben wird und damit die Interpretation des Datums steuert. Die Codes sind in dem Abschnitt style-Codes aufgeführt.
Darüber hinaus kennt SQL Server weitere Formatzeichenfolgen bzw. die Definition von Formatbezeichnern, die von der Funktion FORMAT interpretiert werden können, um ein Datum oder auch Zahl in einen Text entsprechend der angegebenen Formatzeichenfolge zu formatieren. So wird das Datum 24.04.2023 12:34:15.123 gemäß der Formatzeichenfolge yyyyMMddhhmmssfff in den Text 20230418123415123 übersetzt.
1: FORMAT(TRY_CONVERT(datetime, N'24.04.2023 12:34:15.123', 104), 'yyyyMMddhhmmssfff')
2: -- 20230418123415123
Weitere Informationen zu den benutzerdefinierten Formatzeichenfolgen finden sich in dem Abschnitt Formatbezeichner.
Umgekehrt kennt SQL Server leider keine Konvertierungsfunktion, die ein Datum/Uhrzeit, das als Text entsprechend einer Formatzeichenfolge – wie im vorigen Abschnitt beschrieben – vorliegt, interpretiert. Für die Konvertierung eines Datums, das als Text gemäß einer Formatzeichenfolge übergeben wird, sind daher benutzerdefinierte gespeicherte Funktionen zu entwickeln. Dieser Artikel stellt vier benutzerdefinierte gespeicherte Funktionen für die Konvertierung eines Eingangswertes in die Datentypen date, time, datetime oder datetime2 vor:
Der Code dieser Funktionen findet sich in dem Abschnitt Benutzerdefinierte Funktionen für die Konvertierung eines Datums.
Das Rückgabeergebnis hängt vom Zieldatentyp ab — dieser legt den unterstützten Wertebereich und (bei Uhrzeit-Anteilen) die Genauigkeit fest:
date: 01.01.0001 bis 31.12.9999, ohne Uhrzeit.time: bis zu 7 Nachkommastellen bei Sekundenbruchteilen (100-Nanosekunden-Auflösung).datetime: 01.01.1753 bis 31.12.9999, Granularität ~3,33 ms — Sekundenbruchteile werden auf.000,.003oder.007gerundet, nicht frei wählbar auf drei Dezimalstellen.datetime2: 01.01.0001 bis 31.12.9999, bis zu 7 Nachkommastellen, ISO-8601-konform.
Für neue Entwicklungen empfiehlt Microsoft datetime2 statt datetime. datetime2 hat einen größeren Wertebereich, präzisere Sekundenbruchteile und ISO-8601-konformes Verhalten. Ein detaillierter Vergleich zu Oracle (DATE, TIMESTAMP) und MySQL (DATETIME, TIMESTAMP) ist bewusst ausgeklammert. Die Wertebereiche und Präzisionen anderer RDBMS Anbieter unterscheiden sich erheblich und sind im jeweiligen Hersteller-Handbuch nachzuschlagen.
style-Codes
| Style | Land/Beschreibung | Format (yy) | Format (yyyy) |
|---|---|---|---|
| -/0/100 | Default für datetime & smalldatetime | – | mon dd yyyy hh:miAM |
| 1/101 | United States | mm/dd/yy | mm/dd/yyyy |
| 2/102 | ANSI | yy.mm.dd | yyyy.mm.dd |
| 3/103 | England/Frankreich | dd/mm/yy | dd/mm/yyyy |
| 4/104 | Deutschland | dd.mm.yy | dd.mm.yyyy |
| 10/110 | United States | mm-dd-yy | mm-dd-yyyy |
| 12/112 | ISO | yymmdd | yyyymmdd |
| 13/113 | Europa Default (mit ms, 24h) | dd mon yyyy hh:mi:ss:mmm | dd mon yyyy hh:mi:ss:mmm |
Formatbezeichner
Für die textliche Darstellung eines Datums stellt Microsoft einen Satz an Formatbezeichnern zur Verfügung. Ein Auszug der wichtigsten Bezeichner findet sich in der folgenden Tabelle:
| Formatbezeichner | Beschreibung |
| d | Der Tag des Monats, von 1 bis 31. |
| dd | Der Tag des Monats, von 01 bis 31. |
| ddd | Der abgekürzte Name des Tags der Woche. |
| dddd | Der vollständige Name des Wochentags. |
| f | Die Zehntelsekunde in einem Datums- und Uhrzeitwert. |
| ff | Die Hundertstelsekunde in einem Datums- und Uhrzeitwert. |
| fff | Die Millisekunden in einem Datums- und Uhrzeitwert. |
| h | Die Stunde, von 1 bis 12 (12-Stunden-Format). |
| hh | Die Stunde, von 01 bis 12 (12-Stunden-Format). |
| H | Die Stunde, von 0 bis 23 (24-Stunden-Format). |
| HH | Die Stunde, von 00 bis 23 (24-Stunden-Format). |
| m | Die Minute, von 0 bis 59. |
| mm | Die Minute, von 00 bis 59. |
| M | Der Monat, von 1 bis 12. |
| MM | Der Monat, von 01 bis 12. |
| s | Die Sekunde, von 0 bis 59. |
| ss | Die Sekunde, von 00 bis 59. |
| yy | Das Jahr, von 00 bis 99. |
| yyyy | Das Jahr als vierstellige Zahl. |
Benutzerdefinierte Funktionen
Die Implementierungen der Konvertierungs-Funktionen erwarten in dem Parameter p_date ein Datum, das als Text übergeben wird. Der Parameter p_date_style gibt eine benutzerdefinierte Formatzeichenfolge oder einen der oben genannten style-Parameter an. Die unterstützten Formatzeichenfolgen und style-Codes sind dem Code zu entnehmen. Die erwarteten Formatzeichenfolgen entsprechen nicht exakt den Formatbezeichnern, wie sie in dem Abschnitt Formatbezeichner angegeben sind. Wie oben bereits erwähnt, ist Microsoft in der Definition der Formatzeichenfolgen und Formatbezeichner inkonsequent. Die benutzerdefinierten gespeicherten Funktionen gehen mit diesem Umstand pragmatisch um und interpretieren Formatzeichenfolgen nicht, sondern sie übersetzen sie in den zugehörigen style-Parameter oder die Formatierungszeichenfolge wird konkret abgefragt und implementiert. Formatzeichenfolgen werden Case Insensitive behandelt.
Die nachfolgenden Code-Beispiele zeigen nicht alle Übersetzungen der Formatzeichenfolgen in den zugehörigen style-Parameter. Fehlende Übersetzungen sind durch die Zeichenfolge […] ersetzt. Bei Verwendung dieser Funktionen ist diese Zeichenfolge durch entsprechende Übersetzungen zu ersetzen.
Deklaration
Die Deklaration der benutzerdefinierten gespeicherten Funktionen kann allgemein wie folgt angegeben werden:
Beschreibung
Konvertiert einen übergebenen Eingangswert in den Zieldatentyp date, time, datetime oder datetime2. Kann der Eingangswert nicht umgewandelt werden, wird NULL zurückgegeben. Der übergebene Wert wird Case Insensitive behandelt.
Syntax (Beispiel)
1: [dbo].[fnConvertDate] (@p_date AS nvarchar(50), @p_date_style nvarchar(50))
Argumente
- p_dateGibt den umzuwandelnden Eingangswert an.
- p_date_styleGibt eine Formatzeichenfolge an, gemäß der ein Datum in p_date übergeben wird, oder einen style-Code. Die Formatzeichenfolge oder der style-Parameter steuern die Umwandlung des Datums. Die unterstützten Formatzeichenfolgen und style-Codes sind dem Code zu entnehmen.
Rückgabe
Gibt den umgewandelten Wert als Wert vom Typ date, time, datetime oder datetime2 zurück, wenn die Umwandlung erfolgreich ist. Kann der Eingangswert nicht umgewandelt werden, wird NULL zurückgegeben. Wird NULL oder ein leerer String übergeben, gibt die Funktion NULL zurück.
[dbo].[fnConvertDate]
1: CREATE FUNCTION [dbo].[fnConvertDate] (@p_date AS nvarchar(50), @p_date_style nvarchar(50))
2: RETURNS date
3: AS
4: BEGIN
5: DECLARE @return_value AS date;
6: DECLARE @style AS int;
7:
8: SET @p_date = LTRIM(RTRIM(@p_date));
9: SET @p_date_style = LOWER(LTRIM(RTRIM(@p_date_style)));
10:
11: IF @p_date IS NULL OR @p_date = N''
12: BEGIN
13: SET @return_value = NULL;
14: END
15: ELSE
16: BEGIN
17: SET @style = CASE
18: WHEN @p_date_style IN (N'0', N'100', N'mon dd yyyy hh:miam') THEN 100
19: WHEN @p_date_style IN (N'1', N'mm/dd/yy') THEN 1
20: WHEN @p_date_style IN (N'101', N'mm/dd/yyyy') THEN 101
21: WHEN @p_date_style IN (N'2', N'yy.mm.dd') THEN 2
22: WHEN @p_date_style IN (N'102', N'yyyy.mm.dd') THEN 102
23: WHEN @p_date_style IN (N'3', N'dd/mm/yy') THEN 3
24: WHEN @p_date_style IN (N'103', N'dd/mm/yyyy') THEN 103
25: WHEN @p_date_style IN (N'4', N'dd.mm.yy') THEN 4
26: WHEN @p_date_style IN (N'104', N'dd.mm.yyyy') THEN 104
27: WHEN @p_date_style IN (N'5', N'dd-mm-yy') THEN 5
28: WHEN @p_date_style IN (N'105', N'dd-mm-yyyy') THEN 105
29: WHEN @p_date_style IN (N'6', N'dd mon yy') THEN 6
30: WHEN @p_date_style IN (N'106', N'dd mon yyyy') THEN 106
31: WHEN @p_date_style IN (N'7', N'mon dd, yy') THEN 7
32: WHEN @p_date_style IN (N'107', N'mon dd, yyyy') THEN 107
33: WHEN @p_date_style IN (N'9', N'109', N'mon dd yyyy hh:mi:ss:mmmam') THEN 109
34: WHEN @p_date_style IN (N'10', N'mm-dd-yy') THEN 10
35: WHEN @p_date_style IN (N'110', N'mm-dd-yyyy') THEN 110
36: WHEN @p_date_style IN (N'11', N'yy/mm/dd') THEN 11
37: WHEN @p_date_style IN (N'111', N'yyyy/mm/dd') THEN 111
38: WHEN @p_date_style IN (N'12', N'yymmdd') THEN 12
39: WHEN @p_date_style IN (N'112', N'yyyymmdd') THEN 112
40: WHEN @p_date_style IN (N'13', N'113', N'dd mon yyyy hh:mi:ss:mmm') THEN 113
41: WHEN @p_date_style IN (N'20', N'120', N'yyyy-mm-dd hh:mi:ss') THEN 120
42: WHEN @p_date_style IN (N'21', N'25', N'121', N'yyyy-mm-dd hh:mi:ss.mmm') THEN 121
43: WHEN @p_date_style IN (N'22', N'mm/dd/yy hh:mi:ss am') THEN 22
44: WHEN @p_date_style IN (N'23', N'yyyy-mm-dd') THEN 23
45: WHEN @p_date_style IN (N'126', N'yyyy-mm-ddthh:mi:ss.mmm') THEN 126
46: WHEN @p_date_style IN (N'127', N'yyyy-mm-ddthh:mi:ss.mmmz') THEN 127
47: END;
48:
49: IF @style IS NOT NULL
50: SET @return_value = TRY_CONVERT(date, @p_date, @style);
51: ELSE
52: SET @return_value = NULL;
53: END;
54:
55: RETURN @return_value;
56: END;[dbo].[fnConvertTime]
1: CREATE FUNCTION [dbo].[fnConvertTime] (@p_time AS nvarchar(50), @p_time_style nvarchar(50))
2: RETURNS time(7)
3: AS
4: BEGIN
5: DECLARE @return_value AS time(7);
6: DECLARE @style AS int;
7:
8: SET @p_time = LTRIM(RTRIM(@p_time));
9: SET @p_time_style = LOWER(LTRIM(RTRIM(@p_time_style)));
10:
11: IF @p_time IS NULL OR @p_time = N''
12: BEGIN
13: SET @return_value = NULL;
14: END
15: -- Kompakt-Datum+Zeit ohne Trenner: yyyymmddhhmmss[f...]
16: ELSE IF @p_time_style IN
17: (
18: N'yyyymmddhhmmss', N'yyyymmddhhmmssf',
19: N'yyyymmddhhmmssff', N'yyyymmddhhmmssfff',
20: N'yyyymmddhhmmssffff', N'yyyymmddhhmmssfffff',
21: N'yyyymmddhhmmssffffff', N'yyyymmddhhmmssfffffff'
22: )
23: BEGIN
24: SET @return_value = TRY_CONVERT(time(7)
25: ,SUBSTRING(@p_time, 1, 4) + N'-' + -- yyyy
26: SUBSTRING(@p_time, 5, 2) + N'-' + -- mm
27: SUBSTRING(@p_time, 7, 2) + N' ' + -- dd
28: SUBSTRING(@p_time, 9, 2) + N':' + -- hh
29: SUBSTRING(@p_time, 11, 2) + N':' + -- mi
30: SUBSTRING(@p_time, 13, 2) + -- ss
31: CASE WHEN LEN(@p_time) > 14
32: THEN N'.' + SUBSTRING(@p_time, 15, LEN(@p_time) - 14)
33: ELSE N''
34: END
35: );
36: END
37: -- Kompakt-Zeit ohne Trenner: hhmmss[f...]
38: ELSE IF @p_time_style IN
39: (
40: N'hhmmss', N'hhmmssf',
41: N'hhmmssff', N'hhmmssfff',
42: N'hhmmssffff', N'hhmmssfffff',
43: N'hhmmssffffff', N'hhmmssfffffff'
44: )
45: BEGIN
46: SET @return_value = TRY_CONVERT(time(7)
47: ,SUBSTRING(@p_time, 1, 2) + N':' + -- hh
48: SUBSTRING(@p_time, 3, 2) + N':' + -- mi
49: SUBSTRING(@p_time, 5, 2) + -- ss
50: CASE WHEN LEN(@p_time) > 6
51: THEN N'.' + SUBSTRING(@p_time, 7, LEN(@p_time) - 6)
52: ELSE N''
53: END
54: );
55: END
56: -- Klassische MS-style-Codes mit Zeit-Anteil
57: ELSE
58: BEGIN
59: SET @style = CASE
60: WHEN @p_time_style IN (N'0', N'100', N'mon dd yyyy hh:miam') THEN 100
61: WHEN @p_time_style IN (N'8', N'24', N'108', N'hh:mi:ss') THEN 108
62: WHEN @p_time_style IN (N'9', N'109', N'mon dd yyyy hh:mi:ss:mmmam') THEN 109
63: WHEN @p_time_style IN (N'13', N'113', N'dd mon yyyy hh:mi:ss:mmm') THEN 113
64: WHEN @p_time_style IN (N'14', N'114', N'hh:mi:ss:mmm') THEN 114
65: WHEN @p_time_style IN (N'20', N'120', N'yyyy-mm-dd hh:mi:ss') THEN 120
66: WHEN @p_time_style IN (N'21', N'25', N'121', N'yyyy-mm-dd hh:mi:ss.mmm') THEN 121
67: WHEN @p_time_style IN (N'22', N'mm/dd/yy hh:mi:ss am') THEN 22
68: WHEN @p_time_style IN (N'126', N'yyyy-mm-ddthh:mi:ss.mmm') THEN 126
69: WHEN @p_time_style IN (N'127', N'yyyy-mm-ddthh:mi:ss.mmmz') THEN 127
70: END;
71:
72: IF @style IS NOT NULL
73: SET @return_value = TRY_CONVERT(time(7), @p_time, @style);
74: ELSE
75: SET @return_value = NULL;
76: END;
77:
78: RETURN @return_value;
79: END;
[dbo].[fnConvertDateTime]
1: CREATE FUNCTION [dbo].[fnConvertDateTime] (@p_date AS nvarchar(50), @p_date_style nvarchar(50))
2: RETURNS datetime
3: AS
4: BEGIN
5: DECLARE @return_value AS datetime;
6: DECLARE @style AS int;
7:
8: SET @p_date = LTRIM(RTRIM(@p_date));
9: SET @p_date_style = LOWER(LTRIM(RTRIM(@p_date_style)));
10:
11: IF @p_date IS NULL OR @p_date = N''
12: BEGIN
13: SET @return_value = NULL;
14: END
15: -- Kompakt-Datum+Zeit ohne Trenner: yyyymmddhhmmss[fff]
16: ELSE IF @p_date_style IN
17: (
18: N'yyyymmddhhmmss', N'yyyymmddhhmmssf',
19: N'yyyymmddhhmmssff', N'yyyymmddhhmmssfff'
20: )
21: BEGIN
22: SET @return_value = TRY_CONVERT(datetime
23: ,SUBSTRING(@p_date, 1, 4) + N'-' + -- yyyy
24: SUBSTRING(@p_date, 5, 2) + N'-' + -- mm
25: SUBSTRING(@p_date, 7, 2) + N' ' + -- dd
26: SUBSTRING(@p_date, 9, 2) + N':' + -- hh
27: SUBSTRING(@p_date, 11, 2) + N':' + -- mi
28: SUBSTRING(@p_date, 13, 2) + -- ss
29: CASE WHEN LEN(@p_date) > 14
30: THEN N'.' + SUBSTRING(@p_date, 15, LEN(@p_date) - 14)
31: ELSE N''
32: END
33: );
34: END
35: -- Sonderform mit Unterstrich-Separator: yyyymmdd_hhmissmmm
36: ELSE IF @p_date_style = N'yyyymmdd_hhmissmmm'
37: BEGIN
38: SET @return_value = TRY_CONVERT(datetime
39: ,SUBSTRING(@p_date, 1, 4) + N'-' + -- yyyy
40: SUBSTRING(@p_date, 5, 2) + N'-' + -- mm
41: SUBSTRING(@p_date, 7, 2) + N' ' + -- dd
42: SUBSTRING(@p_date, 10, 2) + N':' + -- hh (nach Unterstrich)
43: SUBSTRING(@p_date, 12, 2) + N':' + -- mi
44: SUBSTRING(@p_date, 14, 2) + N'.' + -- ss
45: SUBSTRING(@p_date, 16, 3) -- mmm
46: );
47: END
48: -- Klassische MS-style-Codes
49: ELSE
50: BEGIN
51: SET @style = CASE
52: WHEN @p_date_style IN (N'0', N'100', N'mon dd yyyy hh:miam') THEN 100
53: WHEN @p_date_style IN (N'1', N'mm/dd/yy') THEN 1
54: WHEN @p_date_style IN (N'101', N'mm/dd/yyyy') THEN 101
55: WHEN @p_date_style IN (N'2', N'yy.mm.dd') THEN 2
56: WHEN @p_date_style IN (N'102', N'yyyy.mm.dd') THEN 102
57: WHEN @p_date_style IN (N'3', N'dd/mm/yy') THEN 3
58: WHEN @p_date_style IN (N'103', N'dd/mm/yyyy') THEN 103
59: WHEN @p_date_style IN (N'4', N'dd.mm.yy') THEN 4
60: WHEN @p_date_style IN (N'104', N'dd.mm.yyyy') THEN 104
61: WHEN @p_date_style IN (N'5', N'dd-mm-yy') THEN 5
62: WHEN @p_date_style IN (N'105', N'dd-mm-yyyy') THEN 105
63: WHEN @p_date_style IN (N'6', N'dd mon yy') THEN 6
64: WHEN @p_date_style IN (N'106', N'dd mon yyyy') THEN 106
65: WHEN @p_date_style IN (N'7', N'mon dd, yy') THEN 7
66: WHEN @p_date_style IN (N'107', N'mon dd, yyyy') THEN 107
67: WHEN @p_date_style IN (N'8', N'24', N'108', N'hh:mi:ss') THEN 108
68: WHEN @p_date_style IN (N'9', N'109', N'mon dd yyyy hh:mi:ss:mmmam') THEN 109
69: WHEN @p_date_style IN (N'10', N'mm-dd-yy') THEN 10
70: WHEN @p_date_style IN (N'110', N'mm-dd-yyyy') THEN 110
71: WHEN @p_date_style IN (N'11', N'yy/mm/dd') THEN 11
72: WHEN @p_date_style IN (N'111', N'yyyy/mm/dd') THEN 111
73: WHEN @p_date_style IN (N'12', N'yymmdd') THEN 12
74: WHEN @p_date_style IN (N'112', N'yyyymmdd') THEN 112
75: WHEN @p_date_style IN (N'13', N'113', N'dd mon yyyy hh:mi:ss:mmm') THEN 113
76: WHEN @p_date_style IN (N'14', N'114', N'hh:mi:ss:mmm') THEN 114
77: WHEN @p_date_style IN (N'20', N'120', N'yyyy-mm-dd hh:mi:ss') THEN 120
78: WHEN @p_date_style IN (N'21', N'25', N'121', N'yyyy-mm-dd hh:mi:ss.mmm') THEN 121
79: WHEN @p_date_style IN (N'22', N'mm/dd/yy hh:mi:ss am') THEN 22
80: WHEN @p_date_style IN (N'23', N'yyyy-mm-dd') THEN 23
81: WHEN @p_date_style IN (N'126', N'yyyy-mm-ddthh:mi:ss.mmm') THEN 126
82: WHEN @p_date_style IN (N'127', N'yyyy-mm-ddthh:mi:ss.mmmz') THEN 127
83: END;
84:
85: IF @style IS NOT NULL
86: SET @return_value = TRY_CONVERT(datetime, @p_date, @style);
87: ELSE
88: SET @return_value = NULL;
89: END;
90:
91: RETURN @return_value;
92: END;
[dbo].[fnConvertDateTime2]
1: CREATE FUNCTION [dbo].[fnConvertDateTime2] (@p_date AS nvarchar(50), @p_date_style nvarchar(50))
2: RETURNS datetime2(7)
3: AS
4: BEGIN
5: DECLARE @return_value AS datetime2(7);
6: DECLARE @style AS int;
7:
8: SET @p_date = LTRIM(RTRIM(@p_date));
9: SET @p_date_style = LOWER(LTRIM(RTRIM(@p_date_style)));
10:
11: IF @p_date IS NULL OR @p_date = N''
12: BEGIN
13: SET @return_value = NULL;
14: END
15: -- Kompakt-Datum+Zeit ohne Trenner: yyyymmddhhmmss[f...] (bis 7 Stellen)
16: ELSE IF @p_date_style IN
17: (
18: N'yyyymmddhhmmss', N'yyyymmddhhmmssf',
19: N'yyyymmddhhmmssff', N'yyyymmddhhmmssfff',
20: N'yyyymmddhhmmssffff', N'yyyymmddhhmmssfffff',
21: N'yyyymmddhhmmssffffff', N'yyyymmddhhmmssfffffff'
22: )
23: BEGIN
24: SET @return_value = TRY_CONVERT(datetime2(7)
25: ,SUBSTRING(@p_date, 1, 4) + N'-' + -- yyyy
26: SUBSTRING(@p_date, 5, 2) + N'-' + -- mm
27: SUBSTRING(@p_date, 7, 2) + N' ' + -- dd
28: SUBSTRING(@p_date, 9, 2) + N':' + -- hh
29: SUBSTRING(@p_date, 11, 2) + N':' + -- mi
30: SUBSTRING(@p_date, 13, 2) + -- ss
31: CASE WHEN LEN(@p_date) > 14
32: THEN N'.' + SUBSTRING(@p_date, 15, LEN(@p_date) - 14)
33: ELSE N''
34: END
35: );
36: END
37: -- Sonderform mit Unterstrich-Separator: yyyymmdd_hhmissmmm
38: ELSE IF @p_date_style = N'yyyymmdd_hhmissmmm'
39: BEGIN
40: SET @return_value = TRY_CONVERT(datetime2(7)
41: ,SUBSTRING(@p_date, 1, 4) + N'-' + -- yyyy
42: SUBSTRING(@p_date, 5, 2) + N'-' + -- mm
43: SUBSTRING(@p_date, 7, 2) + N' ' + -- dd
44: SUBSTRING(@p_date, 10, 2) + N':' + -- hh
45: SUBSTRING(@p_date, 12, 2) + N':' + -- mi
46: SUBSTRING(@p_date, 14, 2) + N'.' + -- ss
47: SUBSTRING(@p_date, 16, 3) -- mmm
48: );
49: END
50: -- Klassische MS-style-Codes
51: ELSE
52: BEGIN
53: SET @style = CASE
54: WHEN @p_date_style IN (N'0', N'100', N'mon dd yyyy hh:miam') THEN 100
55: WHEN @p_date_style IN (N'1', N'mm/dd/yy') THEN 1
56: WHEN @p_date_style IN (N'101', N'mm/dd/yyyy') THEN 101
57: WHEN @p_date_style IN (N'2', N'yy.mm.dd') THEN 2
58: WHEN @p_date_style IN (N'102', N'yyyy.mm.dd') THEN 102
59: WHEN @p_date_style IN (N'3', N'dd/mm/yy') THEN 3
60: WHEN @p_date_style IN (N'103', N'dd/mm/yyyy') THEN 103
61: WHEN @p_date_style IN (N'4', N'dd.mm.yy') THEN 4
62: WHEN @p_date_style IN (N'104', N'dd.mm.yyyy') THEN 104
63: WHEN @p_date_style IN (N'5', N'dd-mm-yy') THEN 5
64: WHEN @p_date_style IN (N'105', N'dd-mm-yyyy') THEN 105
65: WHEN @p_date_style IN (N'6', N'dd mon yy') THEN 6
66: WHEN @p_date_style IN (N'106', N'dd mon yyyy') THEN 106
67: WHEN @p_date_style IN (N'7', N'mon dd, yy') THEN 7
68: WHEN @p_date_style IN (N'107', N'mon dd, yyyy') THEN 107
69: WHEN @p_date_style IN (N'8', N'24', N'108', N'hh:mi:ss') THEN 108
70: WHEN @p_date_style IN (N'9', N'109', N'mon dd yyyy hh:mi:ss:mmmam') THEN 109
71: WHEN @p_date_style IN (N'10', N'mm-dd-yy') THEN 10
72: WHEN @p_date_style IN (N'110', N'mm-dd-yyyy') THEN 110
73: WHEN @p_date_style IN (N'11', N'yy/mm/dd') THEN 11
74: WHEN @p_date_style IN (N'111', N'yyyy/mm/dd') THEN 111
75: WHEN @p_date_style IN (N'12', N'yymmdd') THEN 12
76: WHEN @p_date_style IN (N'112', N'yyyymmdd') THEN 112
77: WHEN @p_date_style IN (N'13', N'113', N'dd mon yyyy hh:mi:ss:mmm') THEN 113
78: WHEN @p_date_style IN (N'14', N'114', N'hh:mi:ss:mmm') THEN 114
79: WHEN @p_date_style IN (N'20', N'120', N'yyyy-mm-dd hh:mi:ss') THEN 120
80: WHEN @p_date_style IN (N'21', N'25', N'121', N'yyyy-mm-dd hh:mi:ss.mmm') THEN 121
81: WHEN @p_date_style IN (N'22', N'mm/dd/yy hh:mi:ss am') THEN 22
82: WHEN @p_date_style IN (N'23', N'yyyy-mm-dd') THEN 23
83: WHEN @p_date_style IN (N'126', N'yyyy-mm-ddthh:mi:ss.mmm') THEN 126
84: WHEN @p_date_style IN (N'127', N'yyyy-mm-ddthh:mi:ss.mmmz') THEN 127
85: END;
86:
87: IF @style IS NOT NULL
88: SET @return_value = TRY_CONVERT(datetime2(7), @p_date, @style);
89: ELSE
90: SET @return_value = NULL;
91: END;
92:
93: RETURN @return_value;
94: END;
Demo-Aufrufe
Eine kompakte Demonstration der vier Funktionen — produktive Aufrufe entlang der unterstützten style-Codes und Format-Zeichenfolgen, plus Negativ-Fälle, die garantiert NULL zurückgeben.
1: -- -----------------------------------------------------------------------------
2: -- 1) Klassische style-Codes als String oder als sprechender Format-Bezeichner
3: -- -----------------------------------------------------------------------------
4: SELECT [dbo].[fnConvertDate] (N'24.04.2023', N'104' ) AS d1 -- 2023-04-24
5: ,[dbo].[fnConvertDate] (N'24.04.2023', N'dd.mm.yyyy' ) AS d2 -- identisch: Format-String statt Code
6: ,[dbo].[fnConvertDate] (N'2023-04-24', N'23' ) AS d3
7: ,[dbo].[fnConvertDate] (N'24/04/2023', N'103' ) AS d4
8: ,[dbo].[fnConvertDate] (N'20230424', N'112' ) AS d5;
9:
10: -- -----------------------------------------------------------------------------
11: -- 2) Zeit-Anteil
12: -- -----------------------------------------------------------------------------
13: SELECT [dbo].[fnConvertTime] (N'12:34:15', N'hh:mi:ss' ) AS t1
14: ,[dbo].[fnConvertTime] (N'2023-04-24 12:34:15.1234567', N'yyyy-mm-dd hh:mi:ss.mmm' ) AS t2 -- Code 121: Datum wird verworfen, Zeit-Anteil bleibt
15: ,[dbo].[fnConvertTime] (N'123415', N'hhmmss' ) AS t3
16: ,[dbo].[fnConvertTime] (N'1234151234567', N'hhmmssfffffff' ) AS t4;
17:
18: -- -----------------------------------------------------------------------------
19: -- 3) Datum + Zeit als datetime (Granularitaet ~3,33 ms)
20: -- -----------------------------------------------------------------------------
21: SELECT [dbo].[fnConvertDateTime](N'24.04.2023 12:34:15', N'104' ) AS dt1 -- Style 104 ignoriert die Zeit
22: ,[dbo].[fnConvertDateTime](N'2023-04-24T12:34:15.123', N'126' ) AS dt2
23: ,[dbo].[fnConvertDateTime](N'20230424123415', N'yyyymmddhhmmss' ) AS dt3
24: ,[dbo].[fnConvertDateTime](N'20230424123415123', N'yyyymmddhhmmssfff' ) AS dt4
25: ,[dbo].[fnConvertDateTime](N'20230424_123415123', N'yyyymmdd_hhmissmmm' ) AS dt5;
26:
27: -- -----------------------------------------------------------------------------
28: -- 4) datetime2 mit voller 100-Nanosekunden-Aufloesung
29: -- -----------------------------------------------------------------------------
30: SELECT [dbo].[fnConvertDateTime2](N'2023-04-24T12:34:15.1234567', N'126' ) AS d21
31: ,[dbo].[fnConvertDateTime2](N'24.04.2023', N'dd.mm.yyyy' ) AS d22
32: ,[dbo].[fnConvertDateTime2](N'20230424123415', N'yyyymmddhhmmss' ) AS d23
33: ,[dbo].[fnConvertDateTime2](N'20230424123415123', N'yyyymmddhhmmssfff' ) AS d24
34: ,[dbo].[fnConvertDateTime2](N'202304241234151234567', N'yyyymmddhhmmssfffffff' ) AS d25;
35:
36: -- -----------------------------------------------------------------------------
37: -- 5) Negative Faelle - alle geben NULL zurueck
38: -- -----------------------------------------------------------------------------
39: SELECT [dbo].[fnConvertDate] (N'', N'104' ) AS n1 -- leerer Eingang
40: ,[dbo].[fnConvertDate] (NULL, N'104' ) AS n2 -- NULL
41: ,[dbo].[fnConvertDate] (N'24.04.2023', N'unbekannt' ) AS n3 -- unbekannter Format-String
42: ,[dbo].[fnConvertDate] (N'32.04.2023', N'104' ) AS n4 -- ungueltiger Tag
43: ,[dbo].[fnConvertDate] (N'irgendwas', N'104' ) AS n5; -- nicht parsebar
Postgres-Brücke
Wer ETL-Strecken aus SQL Server nach Postgres portiert oder beide Engines parallel betreibt, sucht früher oder später das Pendant zu TRY_CONVERT(date, …, 104). Postgres bietet TO_DATE und TO_TIMESTAMP — beide nehmen Format-Patterns, keine numerischen Style-Codes. Damit ist die Aufruf-Form näher an der Schnittstelle unserer fnConvertDate-Funktion als an der nummerischen style-Code-Logik von TRY_CONVERT:
1: -- SQL Server
2: TRY_CONVERT(date, '24.04.2023', 104); -- 2023-04-24
3:
4: -- Postgres
5: SELECT TO_DATE ('24.04.2023', 'DD.MM.YYYY'); -- 2023-04-24
6: SELECT TO_TIMESTAMP ('24.04.2023 12:34:15', 'DD.MM.YYYY HH24:MI:SS'); -- 2023-04-24 12:34:15
Pattern-Korrespondenz für die häufigsten Formate
SQL Server TRY_CONVERT (style) | Postgres TO_DATE / TO_TIMESTAMP (Pattern) | Beispiel-Eingabe |
|---|---|---|
104 — dd.mm.yyyy (Deutsch) | DD.MM.YYYY | 24.04.2023 |
103 — dd/mm/yyyy (Brit./Franz.) | DD/MM/YYYY | 24/04/2023 |
101 — mm/dd/yyyy (USA) | MM/DD/YYYY | 04/24/2023 |
102 — yyyy.mm.dd (ANSI) | YYYY.MM.DD | 2023.04.24 |
112 — yyyymmdd (ISO) | YYYYMMDD | 20230424 |
23 — yyyy-mm-dd (ISO 8601) | YYYY-MM-DD | 2023-04-24 |
120 — yyyy-mm-dd hh:mi:ss (ODBC) | YYYY-MM-DD HH24:MI:SS | 2023-04-24 12:34:15 |
121 — yyyy-mm-dd hh:mi:ss.mmm (ODBC mit ms) | YYYY-MM-DD HH24:MI:SS.MS | 2023-04-24 12:34:15.123 |
126 — yyyy-mm-ddThh:mi:ss.mmm (ISO 8601) | YYYY-MM-DD"T"HH24:MI:SS.MS | 2023-04-24T12:34:15.123 |
127 — ISO 8601 mit Zeitzone Z | YYYY-MM-DD"T"HH24:MI:SS.MS"Z" | 2023-04-24T12:34:15.123Z |
NULL statt Exception: Eigenes try_to_date
Anders als TRY_CONVERT liefert TO_DATE bei einem Format-Mismatch kein NULL, sondern wirft eine invalid_datetime_format-Exception (SQLSTATE 22007). Für Bulk-Imports, die mit Fehl-Datensätzen graceful umgehen sollen, kapselt man das in einer kleinen PL/pgSQL-Funktion:
1: CREATE OR REPLACE FUNCTION try_to_date (p_text text, p_pattern text)
2: RETURNS date
3: LANGUAGE plpgsql
4: AS $$
5: BEGIN
6: RETURN TO_DATE(p_text, p_pattern);
7: EXCEPTION
8: WHEN OTHERS THEN
9: RETURN NULL;
10: END;
11: $$;
12:
13: SELECT try_to_date('irgendwas', 'DD.MM.YYYY'); -- NULL statt Exception
14: SELECT try_to_date('24.04.2023', 'DD.MM.YYYY'); -- 2023-04-24
Locale-Abhängigkeit beachten
Monatsnamen-Patterns (MON, MONTH, DY, DAY) hängen vom lc_time-Setting der Session ab. TO_DATE('24-Apr-2023', 'DD-Mon-YYYY') funktioniert nur, wenn lc_time auf einer englischen Locale steht — bei deutscher Locale erkennt Postgres die US-Abkürzung Apr nicht und wirft eine invalid_datetime_format-Exception. Im ETL-Kontext also ist entweder SET lc_time = 'en_US.UTF-8' vor dem Aufruf fixen — oder es sind rein-numerische Patterns (DD, MM, YYYY) verwenden, die Locale-agnostisch sind.
Zusammenfassung
SQL Server unterstützt bei der Konvertierung eines Textes in ein Datum nur die unter CAST and CONVERT (Transact-SQL) dokumentierten Datumsformate und weist den dort aufgelisteten Formaten einen style-Code zu.
Wenn Daten aus Quellsystemen über eine Text-Datei (CSV, XML, JSON) geliefert werden, muss unbedingt geklärt werden, in welchem Format ein Datum geliefert wird. Existiert zu einem Datumsformat kein style-Code, ist eine benutzerdefinierte Funktion zu entwickeln, die die Konvertierung durchführt.
Dieser Artikel stellt vier Funktionen vor, die ein Datum in die folgenden Datentypen konvertieren: date, time, datetime oder datetime2. Die Funktionen übersetzen eine zu übergebende Formatzeichenfolge in einen style-Code und konvertieren das ebenfalls übergebene Datum in den jeweiligen Zieldatentyp.
Take-Away:
TRY_CONVERTmityyyy–style-Code (z. B.104,121,126) ist die deterministische Erste-Wahl-Lösung für CSV-/JSON-Datums-Imports —yy-Codes sind wegen Locale-Sensitivität vermeiden.- Wenn das Quellformat als sprechende Format-Zeichenfolge (
dd.mm.yyyy) bekannt ist, nimm diefn-Wrapper aus diesem Artikel. Sie sind lesbar und ohne Magic-Numbers. - Für neue Anwendungsfälle sollte immer
datetime2(7)stattdatetimeverwendet werden.datetime2bietet einen größeren Wertebereich, präzisere Sekundenbruchteile und ein ISO-8601-konformes Verhalten. - Beim Cross-Engine-ETL sind die Postgres-Pendants
TO_DATE/TO_TIMESTAMPmit Format-Patterns zu benutzen. Sie sind Locale-agnostisch über rein-numerische Patterns (DD,MM,YYYY) zu verwenden.
FAQ
Warum gibt TRY_CONVERT NULL statt einer Fehlermeldung?
TRY_CONVERT ist das fehlertolerante Gegenstück zu CONVERT. Wenn die Konvertierung scheitert — falsches Format, ungültiger Wertebereich, Locale-Mismatch — bekommst du NULL statt einer Exception, die die ganze Pipeline abbricht. Genau das macht die Funktion für ETL-Bulk-Imports brauchbar: Fehl-Datensätze landen in einer Reject-Spalte statt das Insert zu sprengen. Wenn du explizit das harte Verhalten brauchst (z. B. um Format-Bugs früh aufzudecken), nimm CONVERT ohne TRY_.
Was tun bei gemischten Sprach-Formaten in einer Spalte?
Ein CSV-Import kann durchaus 24.04.2023 (Deutsch), 04/24/2023 (USA) und 2023-04-24 (ISO) gemischt enthalten. TRY_CONVERT mit einem fest gewählten Style erschlägt nur eines der drei. Als pragmatische Lösung sind mehrere TRY_CONVERT-Aufrufe mit COALESCE zu kaskadieren — der erste, der nicht NULL zurückgibt, gewinnt:
1: COALESCE(
2: TRY_CONVERT(date, @value, 104), -- dd.mm.yyyy
3: TRY_CONVERT(date, @value, 101), -- mm/dd/yyyy
4: TRY_CONVERT(date, @value, 23) -- yyyy-mm-dd (ISO 8601)
5: )
Die Reihenfolge ist so wählen, dass das wahrscheinlichste Quellformat zuerst getestet wird.
datetime oder datetime2 — welcher Datentyp wann?
Für neue Entwicklungen ist datetime2(7) die richtige Wahl. datetime2(7) bietet einen größeren Wertebereich (ab 0001-01-01), eine höhere Genauigkeit (100 ns statt ~3,33 ms) und ein ISO-8601-konformes Parsing. datetime bleibt nur relevant, wenn ein Legacy-Schema darauf festgenagelt ist oder die enge Speichergröße ein konkretes Optimierungs-Argument liefert. Im Zweifel: datetime2(7).
Hängt das Konvertierungs-Ergebnis vom Server-SET LANGUAGE / SET DATEFORMAT ab?
Ja — und zwar genau bei den style-Codes, die laut MS-Doku „nicht-deterministisch“ sind: Style 0/100, 9/109, 13/113 und alle yy-Varianten (1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 22) parsen Monatsnamen oder zweistellige Jahre kontextabhängig. Apr wird je nach Server-Locale als Englisch erkannt — auf einem deutsch-eingestellten Server nicht. Für deterministische ETL-Pipelines sind daher immer die yyyy-Varianten (z. B. 104 statt 4) und die ISO-Codes (23, 121, 126) bevorzugen.
Postgres-Pendant für TRY_CONVERT(date, …, 104)?
TO_DATE('24.04.2023', 'DD.MM.YYYY') — siehe Postgres-Brücke für die Pattern-Korrespondenz-Tabelle und die try_to_date-Wrapper-Funktion, die das NULL-Verhalten von TRY_CONVERT nachbildet.
Verwandte Artikel
ETL-Kontext:
- Design Pattern // Architektur eines ETL-Prozesses
- Datenqualität in einem ETL-Prozess
- Design Pattern // Sichere Typ-Konvertierung mit T-SQL
- Datenqualität // Grundlagen der Typ-Konvertierung mit T-SQL
TRY_CONVERT für andere Datentypen:
2 Kommentare zu „Datenqualität in SQL Server // TRY_CONVERT für date, datetime, datetime2 und time sicher anwenden“
Die Kommentare sind geschlossen.