Inhalt
- Überblick
- style-Codes
- Formatbezeichner
- Benutzerdefinierte Funktionen
- Zusammenfassung
- 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 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.2023Die 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 sytle-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: -- > 20230418123415123Weitere 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 in erster Linie von dem Zieldatentyp ab, da dieser den Wertebereich eines Datums und bei Zeitangaben die Genauigkeit festgelegt. Während SQL Server bei den Datentypen date und datetime einen Wertbereich von 01.01.1753 bis 31.12.9999 und der Datentyp datetime2 einen Wertbereich von 01.01.0001 bis 31.12.9999 unterstützt, unterstützen zum Beispiel die Datenbanksysteme Oracle und MySQL mit den Datentypen DATE, DATETIME und TIMESTAMP den Wertebereich 01.01.0000 bis 31.12.9999. Enthält das Datum auch eine Uhrzeit, ist die Genauigkeit in Sekundenbruchteilen, mit der eine Uhrzeit zu speichern ist, zu berücksichtigen. Die Oracle-Datentypen DATETIME und TIMESTAMP unterstützen generell die Speicherung von Sekundenbruchteilen mit 7 Nachkommastellen. In SQL Server unterstützen die Datentypen datetime2 und time die Speicherung von 7 Nachkommastellen, während der Datentyp datetime nur 3 Nachkommastellen unterstützt.
style-Codes
| Style | Wertebereich | |||
| yy | yyyy | Land/Beschreibung | Formatzeichenfolge | |
| – | 0 oder 100 | Default für datetime und smalldatetime | mon dd yyyy hh:miAM | |
| 1 | 101 | United States | 1 = | mm/dd/yy |
| 101 = | mm/dd/yyyy | |||
| 2 | 102 | ANSI | 2 = | yy.mm.dd |
| 102 = | yyyy.mm.dd | |||
| 3 | 103 | England/Frankreich | 3 = | dd/mm/yy |
| 103 = | dd/mm/yyyy | |||
| 4 | 104 | Deutschland | 4 = | dd.mm.yy |
| 104 = | dd.mm.yyyy | |||
| 10 | 110 | United States | 10 = | mm-dd-yy |
| 110 = | mm-dd-yyyy | |||
| 12 | 112 | ISO | 12 = | yymmdd |
| 112 = | yyyymmdd | |||
| 13 | 113 | Europa (Default) inklusive Zeit mit Millisekunden | 13 = | dd mon yyyy hh:mi:ss:mmm |
| 113 = | (24 Stunden) | |||
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_DateStyle 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 Funktionenkann 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
1: [dbo].[fnConvertDate] (@p_Date AS nvarchar(50), @p_DateStyle nvarchar(50))Argumente
- p_Date
Gibt den umzuwandelnden Eingangswert an. - p_DateStyle
Gibt 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_DateStyle nvarchar(50))
2: RETURNS date
3: AS
4: BEGIN
5: -- --------------------------------------------------------------------------------
6: -- Declare variables
7: -- --------------------------------------------------------------------------------
8: DECLARE @returnValue AS date;
9:
10: SET @p_DateStyle = LOWER(@p_DateStyle);
11:
12: -- --------------------------------------------------------------------------------
13: -- Workload
14: -- --------------------------------------------------------------------------------
15: IF [dbo].[fnIsNullOrEmpty](@p_Date, 1) = 1
16: BEGIN
17: SET @returnValue = NULL;
18: END
19: ELSE IF @p_DateStyle IN (
20: N'yyyy-mm-dd' , N'23'
21: ,N'mon dd yyyy hh:miam' , N'100'
22: ,N'mm/dd/yyyy' , N'101'
23: ,N'yyyy.mm.dd' , N'102'
24: ,[...]
25: )
26: BEGIN
27: SET @returnValue = CASE
28: WHEN @p_DateStyle IN (N'yyyy-mm-dd' , N'23' )
29: THEN TRY_CONVERT(date, @p_Date, 23)
30: WHEN @p_DateStyle IN (N'mon dd yyyy hh:miam', N'100')
31: THEN TRY_CONVERT(date, @p_Date, 100)
32: WHEN @p_DateStyle IN (N'mm/dd/yyyy' , N'101')
33: THEN TRY_CONVERT(date, @p_Date, 101)
34: WHEN @p_DateStyle IN (N'yyyy.mm.dd' , N'102')
35: THEN TRY_CONVERT(date, @p_Date, 102)
36: WHEN @p_DateStyle IN (N'dd/mm/yyyy' , N'103')
37: THEN TRY_CONVERT(date, @p_Date, 103)
38: [...]
39: END;
40: END
41: ELSE
42: BEGIN
43: SET @returnValue = NULL;
44: END;
45:
46: RETURN @returnValue;
47: END;
[dbo].[fnConvertTime]
1: CREATE FUNCTION [dbo].[fnConvertTime](@p_Time AS nvarchar(50), @p_TimeStyle nvarchar(50))
2: RETURNS time(7)
3: AS
4: BEGIN
5: -- --------------------------------------------------------------------------------
6: -- Declare variables
7: -- --------------------------------------------------------------------------------
8: DECLARE @returnValue AS time;
9:
10: -- --------------------------------------------------------------------------------
11: -- Initialize variables
12: -- --------------------------------------------------------------------------------
13: SET @p_TimeStyle = LOWER(@p_TimeStyle)
14:
15: -- --------------------------------------------------------------------------------
16: -- Workload
17: -- --------------------------------------------------------------------------------
18: IF [dbo].[fnIsNullOrEmpty](@p_Time, 1) = 1
19: BEGIN
20: SET @returnValue = NULL;
21: END
22: ELSE IF @p_TimeStyle IN ( N'yyyymmddhhmmss'
23: ,N'yyyymmddhhmmssf'
24: ,N'yyyymmddhhmmssff'
25: ,[...]
26: ,N'yyyymmddhhmmssfffffff')
27: BEGIN
28: SET @returnValue = TRY_CONVERT( time(7) -- 202304181011121234567
29: ,SUBSTRING(@p_Time, 1, 4)+'-'+ -- 2023
30: SUBSTRING(@p_Time, 5, 2)+'-'+ -- 04
31: SUBSTRING(@p_Time, 7, 2)+' '+ -- 18
32: SUBSTRING(@p_Time, 9, 2)+':'+ -- 10
33: SUBSTRING(@p_Time,11, 2)+':'+ -- 11
34: SUBSTRING(@p_Time,13, 2)+'.'+ -- 12
35: SUBSTRING(@p_Time,15, 7) -- 1234567
36: ,120
37: )
38: END
39: ELSE IF @p_TimeStyle IN ( N'hhmmss'
40: ,N'hhmmssf'
41: ,N'hhmmssff'
42: ,N'hhmmssfff'
43: ,[...]
44: ,N'hhmmssfffffff')
45: BEGIN
46: SET @returnValue = TRY_CONVERT( time(7) -- 1011121234567
47: ,SUBSTRING(@p_Time, 1, 2)+':'+ -- 10
48: SUBSTRING(@p_Time, 3, 2)+':'+ -- 11
49: SUBSTRING(@p_Time, 5, 2)+'.'+ -- 12
50: SUBSTRING(@p_Time, 7, 7) -- 1234567
51: ,120
52: )
53: END
54: ELSE IF @p_TimeStyle IN (
55: N'mon dd yyyy hh:miam' , N'100'
56: ,N'mm/dd/yyyy' , N'101'
57: ,N'yyyy.mm.dd' , N'102'
58: ,N'dd/mm/yyyy' , N'103'
59: ,[...]
60: )
61: BEGIN
62: SET @returnValue = CASE
63: WHEN @p_TimeStyle IN (N'yyyy.mm.dd' , N'102')
64: THEN TRY_CONVERT(time(7), @p_Time, 102)
65: WHEN @p_TimeStyle IN (N'yyyy-mm-dd hh:mi:ss' , N'120')
66: THEN TRY_CONVERT(time(7), @p_Time, 120)
67: WHEN @p_TimeStyle IN (N'yyyy-mm-dd hh:mi:ss.mmm' , N'121')
68: THEN TRY_CONVERT(time(7), @p_Time, 121)
69: WHEN @p_TimeStyle IN (N'yyyy-mm-ddthh:mi:ss.mmm' , N'126')
70: THEN TRY_CONVERT(time(7), @p_Time, 126)
71: [...]
72: END;
73: END
74: ELSE
75: BEGIN
76: SET @returnValue = NULL;
77: END;
78:
79: RETURN @returnValue;
80: END;
[dbo].[fnConvertDateTime]
1: CREATE FUNCTION [dbo].[fnConvertDateTime]
(
@p_date AS nvarchar(50)
,@p_dateStyle AS nvarchar(50)
)
2: RETURNS datetime
3: AS
4: BEGIN
5: -- --------------------------------------------------------------------------------
6: -- Declare variables
7: -- --------------------------------------------------------------------------------
8: DECLARE @returnValue AS datetime;
9:
10: -- --------------------------------------------------------------------------------
11: -- Initialize variables
12: -- --------------------------------------------------------------------------------
13: SET @p_dateStyle = LOWER(@p_dateStyle);
14:
15: -- --------------------------------------------------------------------------------
16: -- Workload
17: -- --------------------------------------------------------------------------------
18: IF [dbo].[fnIsNullOrEmpty](@p_Date, 1) = 1
19: BEGIN
20: SET @returnValue = NULL;
21: END
22: ELSE IF @p_dateStyle IN ( N'yyyymmddhhmmss'
, N'yyyymmddhhmmssf'
, N'yyyymmddhhmmssff'
, N'yyyymmddhhmmssfff'
)
23: BEGIN
24: SET @returnValue = TRY_CONVERT(datetime2 -- 20230418101112123
25: ,SUBSTRING(@p_date, 1, 4)+'-'+ -- 2023
26: SUBSTRING(@p_date, 5, 2)+'-'+ -- 04
27: SUBSTRING(@p_date, 7, 2)+' '+ -- 18
28: SUBSTRING(@p_date, 9, 2)+':'+ -- 10
29: SUBSTRING(@p_date,11, 2)+':'+ -- 11
30: SUBSTRING(@p_date,13, 2)+'.'+ -- 12
31: SUBSTRING(@p_date,15, 3) -- 123
32: ,120
33: )
34: END
35: ELSE IF @p_dateStyle = N'yyyymmdd_hhmissmmm'
36: BEGIN
37: SET @returnValue = TRY_CONVERT(datetime
38: ,SUBSTRING(@p_date, 1, 4)+'-'+
39: SUBSTRING(@p_date, 5, 2)+'-'+
40: SUBSTRING(@p_date, 7, 2)+' '+
41: SUBSTRING(@p_date,10, 2)+':'+
42: SUBSTRING(@p_date,12, 2)+':'+
43: SUBSTRING(@p_date,14, 2)+'.'+
44: SUBSTRING(@p_date,16, 3)
45: ,121
46: )
47: END
48: ELSE IF @p_dateStyle IN (
49: N'yyyy-mm-dd' , N'23'
50: ,N'mon dd yyyy hh:miam', N'100'
51: ,N'mm/dd/yyyy' , N'101'
52: ,N'yyyy.mm.dd' , N'102'
53: ,[...]
54: )
55: BEGIN
56: SET @returnValue = CASE
57: WHEN @p_dateStyle IN (N'yyyy-mm-dd' , N'23' )
58: THEN TRY_CONVERT(datetime, @p_date, 23)
59: WHEN @p_dateStyle IN (N'mon dd yyyy hh:miam', N'100')
60: THEN TRY_CONVERT(datetime, @p_date, 100)
61: WHEN @p_dateStyle IN (N'mm/dd/yyyy' , N'101')
62: THEN TRY_CONVERT(datetime, @p_date, 101)
63: WHEN @p_dateStyle IN (N'yyyy.mm.dd' , N'102')
64: THEN TRY_CONVERT(datetime, @p_date, 102)
65: [...]
66: END;
67: END
68: ELSE
69: BEGIN
70: SET @returnValue = NULL;
71: END;
72:
73: RETURN @returnValue;
74: END;
[dbo].[fnConvertDateTime2]
1: CREATE FUNCTION [dbo].[fnConvertDateTime2] (@p_date AS nvarchar(50), @p_dateStyle nvarchar(50))
2: RETURNS datetime2(7)
3: AS
4: BEGIN
5: -- --------------------------------------------------------------------------------
6: -- Declare variables
7: -- --------------------------------------------------------------------------------
8: DECLARE @returnValue AS datetime2(7);
9:
10: -- --------------------------------------------------------------------------------
11: -- Initialize variables
12: -- --------------------------------------------------------------------------------
13: SET @p_dateStyle = LOWER(@p_dateStyle)
14:
15: -- --------------------------------------------------------------------------------
16: -- Workload
17: -- --------------------------------------------------------------------------------
18: IF [dbo].[fnIsNullOrEmpty](@p_Date, 1) = 1
19: BEGIN
20: SET @returnValue = NULL;
21: END
22: ELSE IF @p_dateStyle IN ( N'yyyymmddhhmmss'
23: ,N'yyyymmddhhmmssf'
24: ,N'yyyymmddhhmmssff'
25: ,[...]
26: ,N'yyyymmddhhmmssfffffff')
27: BEGIN
28: SET @returnValue = TRY_CONVERT( datetime2 -- 202304181011121234567
29: ,SUBSTRING(@p_date, 1, 4)+'-'+ -- 2023
30: SUBSTRING(@p_date, 5, 2)+'-'+ -- 04
31: SUBSTRING(@p_date, 7, 2)+' '+ -- 18
32: SUBSTRING(@p_date, 9, 2)+':'+ -- 10
33: SUBSTRING(@p_date,11, 2)+':'+ -- 11
34: SUBSTRING(@p_date,13, 2)+'.'+ -- 12
35: SUBSTRING(@p_date,15, 7) -- 1234567
36: ,120
37: )
38: END
39: ELSE IF @p_dateStyle = N'yyyymmdd_hhmissmmm'
40: BEGIN
41: SET @returnValue = TRY_CONVERT( datetime2(7)
42: ,SUBSTRING(@p_date, 1, 4)+'-'+
43: SUBSTRING(@p_date, 5, 2)+'-'+
44: SUBSTRING(@p_date, 7, 2)+' '+
45: SUBSTRING(@p_date,10, 2)+':'+
46: SUBSTRING(@p_date,12, 2)+':'+
47: SUBSTRING(@p_date,14, 2)+'.'+
48: SUBSTRING(@p_date,16, 3)
49: ,121
50: )
51: END
52: ELSE IF @p_dateStyle IN (
53: N'mon dd yyyy hh:miam' , N'100'
54: ,N'mm/dd/yyyy' , N'101'
55: ,N'yyyy.mm.dd' , N'102'
56: ,N'dd/mm/yyyy' , N'103'
57: ,[...]
58: )
59: BEGIN
60: SET @returnValue = CASE
61: WHEN @p_dateStyle IN (N'yyyy.mm.dd' , N'102')
62: THEN TRY_CONVERT(datetime2(7), @p_date, 102)
63: WHEN @p_dateStyle IN (N'yyyy/mm/dd' , N'111')
64: THEN TRY_CONVERT(datetime2(7), @p_date, 111)
65: WHEN @p_dateStyle IN (N'yyyymmdd' , N'112')
66: THEN TRY_CONVERT(datetime2(7), @p_date, 112)
67: WHEN @p_dateStyle IN (N'mon dd yyyy hh:miam', N'100')
68: THEN TRY_CONVERT(datetime2(7), @p_date, 100)
69: END;
70: END
71: ELSE
72: BEGIN
73: SET @returnValue = NULL;
74: END;
75:
76: RETURN @returnValue;
77: END;
Zusammenfassung
SQL Server unterstützt bei der Konvertierung eines Textes in ein Datum nur die unter CAST and CONVERT (Transact-SQLL) 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 4 Funktionen vor, die ein Datum in die folgenden Datentypen konvertiert: 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.
Verwandte Artikel
- Datenqualität in einem ETL-Prozess
- Datenqualität // Grundlagen der Typ-Konvertierung mit T-SQL
- TRY_CONVERT // Konvertierung nach bigint, int, smallint, tinyint
- TRY_CONVERT // Konvertierung nach decimal oder numeric
- TRY_CONVERT // Konvertierung nach money, smallmoney
- TRY_CONVERT // Konvertierung nach float, real
- TRY_CONVERT // Konvertierung nach bit
- TRY_CONVERT // Konvertierung nach date, datetime, datetime2, time