Dieser Artikel gehört zu der Artikelserie Datenqualität in einem ETL-Prozess, in der ein Design Pattern vorgestellt wird, das die extrahierte Daten prüft, behandelt und schlechte Daten von der weiteren Verarbeitung ausschließt.
SQL Server stellt mit den T-SQL Funktionen CAST, CONVERT beziehungsweise TRY_CONVERT und TRY_CAST Funktionen für die Typ-Konvertierung zur Verfügung. Die Syntax der Funktionen CONVERT und TRY_CONVERT beziehungsweise CAST und TRY_CAST ist identisch. Der Unterschied liegt einzig darin, dass TRY_CONVERT und TRY_CAST keine Exception werfen, wenn der übergebene Wert nicht in den Zieldatentypen konvertiert werden kann. Die Funktionen geben im Fehlerfall ein NULL zurück.
Zwischen den Funktionen CAST und CONVERT gibt es zwei wesentliche Unterschiede.
Zum einen ist die Funktion CONVERT (und damit auch TRY_CONVERT) eine SQL Server spezifische Funktion und entspricht nicht dem ANSI SQL Standard. In anderen Worten: Die Funktion CAST existiert in Oracle, Postgres und anderen Datenbanksystemen, die Funktion CONVERT mit einer äquivalenten Funktionsweise jedoch (möglicherweise) nicht. Die Funktion TRY_CAST ist ebenfalls eine SQL Server spezifische Funktion.
Zum anderen erlauben die Funktionen TRY_CONVERT und CONVERT im Gegensatz zu den Funktionen CAST und TRY_CAST die Angabe eines Style-Parameters, der von der Funktion für die Interpretation des übergebenen Wertes verwendet wird. Ein Beispiel hierfür ist ein Datum, das als Text an die Funktionen CAST oder CONVERT übergeben wird. Für das Datum gibt es in den Ländern dieser Welt sehr unterschiedliche Schreibweisen. Das Datum 02.11.2024 (Deutsche Schreibweise) wird gemäß der folgenden Tabelle sehr unterschiedlich notiert:
| Land | Formatstring | Datum | Style-Parameter |
|---|---|---|---|
| Deutschland | dd.mm.yyyy | 02.11.2024 | 104 |
| USA | yyyy/mm/dd | 11-02-2024 | 110 |
| Japan | mm-dd-yyyy | 2024/11/02 | 111 |
Eine typsichere Konvertierung kann nur dann erfolgen, wenn wir Kenntnis über das Datumsformat und den korrekten Style-Parameter haben. Die folgenden SELECT Statements verdeutlichen die Problematik:
1: SELECT CAST('02.11.2024' AS date); -- > 2024-02-11
2: SELECT CAST('11-02-2024' AS date); -- > 2024-11-02
3: SELECT CAST('2024/11/02' AS date); -- > 2024-11-02
4:
5: SELECT CONVERT(date, '02.11.2024', 104); -- > 2024-11-02
6: SELECT CONVERT(date, '2024/11/02', 111); -- > 2024-11-02
7: SELECT CONVERT(date, '11-02-2024', 110); -- > 2024-11-02
8:
9: SELECT CONVERT(date, '02.11.2024', 111);
10: -- > Exception: Conversion failed when converting date and/or
11: -- time from character string.
Die zurückgegebenen Werte, die hinter den Kommentarzeichen notiert sind, wurden dem Resultset im SQL Server Management Studio entnommen. Per Default wird ein Datum in dem Resultset in SQL Server Management Studio – solange ein SQL Statement keine explizite Format-Anweisungen enthält – in amerikanischer Notation (also yyyy-MM-dd) ausgegeben. Demnach wird ein Deutsches Datum in Zeile 1 von der Funktion CAST falsch interpretiert. Statt 02.11.2024 erhalten wir als Ergebnis den 11.02.2024. Durch die Angabe des korrekten Style-Parameters 104 in Zeile 5 in der Funktion CONVERT, kann aber auch ein Deutsches Datum korrekt interpretiert werden.
Ohne an dieser Stelle genauer auf die Probleme bei der Typ-Konvertierung in Abhängigkeit von dem Datentyp einzugehen, sollte zwei Dinge klar sein:
- Eine sichere Typ-Konvertierung kann eine herausfordernde Angelegenheit sein. Dieses gilt übrigens auch für vermeintlich einfache Datentypen wie decimal und float.
- Bei der Entwicklung eines ETL-Prozesses, bei dem die zu verarbeitenden Daten aus Dateien extrahiert werden (CSV, XML, JSON, …), ist es unerlässlich, genau festzulegen, in welchem Format ein Datum, eine Zahl, eine Ja/Nein-Information, ein NULL und anderes geliefert werden.
Die Artikelserie Datenqualität // Sichere Typ-Konvertierung mit T-SQL verwendet (fast) ausschließlich die Funktion TRY_CONVERT für die Typ-Konvertierung. Grund hierfür ist die Möglichkeit der Angabe eines Style-Parameters, über den Einfluss auf die Interpretation des Eingangswertes genommen werden kann.
Sofern die hier vorgestellte Systematik der Typ-Konvertierung auf ein anderes Datenbank-System zu portieren ist, sind äquivalente Funktionen zu finden oder gegebenenfalls selbst zu programmieren. Mir ist bewusst, dass die Verwendung einer nicht ANSI SQL Funktion die Portabilität hemmt. Bei Verwendung der Funktion CAST würden zusätzlicher Entwicklungsaufwand entstehen und gegebenenfalls die Les- und Wartbarkeit der erforderlichen T-SQL Artefakte beinträchtigen.
Dieser Artikel gehört zu der Artikelreihe Sichere Typ-Konvertierung mit T-SQL.