Datentypkonvertierung (Datenbankmodul)

Artikel
12/15/2008

Datentypen können in den folgenden Szenarien konvertiert werden:

Wenn Daten aus einem Objekt zu Daten aus einem anderen Objekt verschoben oder mit diesen verglichen oder kombiniert werden, müssen die Daten möglicherweise vom Datentyp des einen Objekts in den Datentyp des anderen Objekts konvertiert werden.
Wenn Daten aus einer Transact-SQL-Ergebnisspalte, einen Rückgabecode oder einem Ausgabeparameter in eine Programmvariable verschoben werden, müssen die Daten vom SQL Server 2005-Systemdatentyp in den Datentyp der Variablen konvertiert werden.

Datentypen können entweder implizit oder explizit konvertiert werden:

Implizite Konvertierungen sind für den Benutzer nicht sichtbar.
SQL Server konvertiert die Daten automatisch von einem Datentyp in einen anderen. Beispielsweise wird beim Vergleich eines smallint-Datentyps mit einem int-Datentyp der smallint-Datentyp implizit in int konvertiert, bevor der Vergleich fortgesetzt wird.
Explizite Konvertierungen verwenden die Funktionen CAST oder CONVERT.
Die Funktionen CAST und CONVERT (Transact-SQL) konvertieren einen Wert (eine lokale Variable, eine Spalte oder einen anderen Ausdruck) von einem Datentyp in einen anderen. Die folgende CAST-Funktion konvertiert z. B. den numerischen Wert $157.27 in die Zeichenfolge '157.27':
```
CAST ( $157.27 AS VARCHAR(10) )
```
Verwenden Sie CAST anstelle von CONVERT, wenn der Transact-SQL-Programmcode mit SQL-92 kompatibel sein soll. Verwenden Sie dagegen CONVERT anstelle von CAST, um die Vorteile der style-Funktionalität in CONVERT zu nutzen.

Einige implizite und explizite Datentypkonvertierungen werden nicht unterstützt, wenn Sie den Datentyp eines SQL Server-Objekts in einen anderen konvertieren. Ein nchar-Wert kann nicht in einen image-Wert konvertiert werden. nchar kann nur mit der expliziten Konvertierung in binary konvertiert werden; eine implizite Konvertierung in binary wird nicht unterstützt. nchar kann jedoch explizit in nvarchar konvertiert werden.

Bei der Verarbeitung von sql_variant-Datentypen unterstützt SQL Server die implizite Konvertierung von Objekten mit anderen Datentypen zum Typ sql_variant. SQL Server unterstützt jedoch keine impliziten Konvertierungen von sql_variant-Daten in ein Objekt mit einem anderen Datentyp.

Weitere Informationen zu den unterstützten Konvertierungen zwischen SQL Server-Objekten finden Sie unter CAST und CONVERT (Transact-SQL).

Bei der Konvertierung zwischen einer Anwendungsvariablen und einer SQL Server-Resultsetspalte, einem Rückgabecode, einem Parameter oder einer Parametermarkierung werden die unterstützten Datentypkonvertierungen von der Datenbank-API (Application Programming Interface, Anwendungsprogrammierschnittstelle) definiert. Weitere Informationen finden Sie unter Verschieben von Daten in Programmvariablen.

Verhalten bei der Datentypkonvertierung

In den folgenden Abschnitten in diesem Thema wird das Konvertierungsverhalten folgender Datentypen beschrieben:

binary- und varbinary-Daten	float- und real-Daten
bit-Daten	money-Daten
Zeichendaten	decimal- und numeric-Daten
datetime- und smalldatetime-Daten	Datentypen mithilfe von gespeicherten Prozeduren der OLE-Automatisierung

Konvertieren von binary- und varbinary-Daten

Wenn Daten von einem string-Datentyp (char, varchar, nchar, nvarchar, binary, varbinary, text, ntext oder image) in einen binary- oder varbinary-Datentyp anderer Länge konvertiert werden, füllt Microsoft SQL Server die Daten nach rechts auf oder schneidet sie rechts ab. Bei der Konvertierung anderer Datentypen in binary oder varbinary werden die Daten nach links aufgefüllt oder links abgeschnitten. Für das Auffüllen werden hexadezimale Nullen verwendet.

Das Konvertieren von Daten in die Datentypen binary und varbinary ist hilfreich, wenn binary-Daten die einfachste Möglichkeit zum Verschieben von Daten darstellen. Beim Konvertieren aller Werte eines beliebigen Datentyps in einen ausreichend großen binären Wert und dem anschließenden Konvertieren in den ursprünglichen Datentyp ergibt sich stets derselbe Wert, wenn beide Konvertierungen mit der gleichen Version von SQL Server ausgeführt werden. Die binäre Darstellung eines Wertes kann sich zwischen den Versionen von SQL Server ändern.

Sie können die Datentypen int, smallint und tinyint in die Datentypen binary oder varbinary konvertieren; wenn Sie allerdings den binary-Wert wieder zurück in einen ganzzahligen Wert konvertieren, kann das Ergebnis von der ursprünglichen ganzen Zahl abweichen, falls der Wert abgeschnitten wurde. Die folgende SELECT-Anweisung zeigt beispielsweise, dass der ganzzahlige Wert 123456 in der Regel als binärer Wert 0x0001e240 gespeichert wird:

SELECT CAST( 123456 AS BINARY(4) )

Diese SELECT-Anweisung zeigt, dass die vorangestellten Nullen automatisch abgeschnitten werden, wenn das binary-Ziel für die Aufnahme des gesamten Wertes zu klein ist. Die gleiche Zahl wird daher als 0xe240 gespeichert:

SELECT CAST( 123456 AS BINARY(2) )

Der folgende Batch zeigt, dass sich das automatische Abschneiden des Wertes auf arithmetische Operationen auswirken kann, ohne dass ein Fehler ausgelöst wird:

DECLARE @BinaryVariable2 BINARY(2)

SET @BinaryVariable2 = 123456
SET @BinaryVariable2 = @BinaryVariable2 + 1

SELECT CAST( @BinaryVariable2 AS INT)
GO

Das endgültige Ergebnis ist 57921 und nicht 123457.

Hinweis:
Konvertierungen zwischen einem beliebigen Datentyp und den binary-Datentypen sind bei unterschiedlichen SQL Server-Versionen nicht unbedingt identisch.

Konvertieren in bit-Daten

Die Konvertierung in den bit-Datentyp ergibt für alle Werte ungleich 0 den Wert 1.

Konvertieren von Zeichendaten

Werden Zeichenausdrücke in einen Zeichendatentyp mit einer anderen Größe konvertiert, dann werden Werte, die für den neuen Datentyp zu lang sind, abgeschnitten.

Wenn ein Zeichenausdruck in einen Zeichenausdruck eines anderen Datentyps oder einer anderen Größe konvertiert wird (char(5) in varchar(5) oder char(20) in char(15)), wird die Sortierung des Eingabewertes dem konvertierten Wert zugewiesen. Wird ein Nichtzeichenausdruck zu einem Zeichendatentyp konvertiert, wird die Standardsortierung der aktuellen Datenbank dem konvertierten Wert zugewiesen. In beiden Fällen können Sie mithilfe der COLLATE-Klausel auch eine bestimmte Sortierung zuweisen.

Hinweis:
Codepageübersetzungen werden für die Datentypen char und varchar, nicht jedoch für den text-Datentyp unterstützt. Wie auch bei früheren Versionen von SQL Server wird der Datenverlust während der Codepageübersetzung nicht gemeldet.

Zeichenausdrücke, die in einen ungefähren numeric-Datentyp konvertiert werden, können die optionale Exponentialschreibweise enthalten (den Kleinbuchstaben e oder den Großbuchstaben E, auf den ein optionales Plus- (+) oder Minuszeichen (-) und dann eine Zahl folgen).

Zeichenausdrücke, die in einen exakten numeric-Datentyp konvertiert werden, müssen aus Ziffern, einem Dezimaltrennzeichen und einem optionalen Plus- (+) oder Minuszeichen (-) zusammengesetzt sein. Führende Leerzeichen werden ignoriert. Kommas als Trennzeichen (z. B. das Trennzeichen in 123.456,00) sind in der Zeichenfolge nicht zulässig.

Zeichenausdrücke, die in die Datentypen money odersmallmoney konvertiert werden, können außerdem ein optionales Dezimaltrennzeichen und ein Dollarzeichen ($) enthalten. Kommas als Trennzeichen (wie in 123.456,00 $) sind zulässig.

Dieses Beispiel zeigt, wie Daten für die Anzeige konvertiert werden. Dieses Beispiel konvertiert die Verkaufsdaten in Zeichendaten, bevor ein Zeichenfolgenvergleich vorgenommen und das aktuelle Datum in das Format 3 (tt/mm/jj) konvertiert wird.

USE AdventureWorks;
GO
SELECT SalesPersonID,
   CAST(SalesYTD AS varchar(12)),
   CONVERT(VARCHAR(12), GETDATE(), 3)
FROM Sales.SalesPerson
WHERE CAST(SalesYTD AS varchar(20) ) LIKE '1%'
GO

Dieses Beispiel konvertiert einen uniqueidentifier-Wert in einen char-Datentyp.

DECLARE @myid uniqueidentifier
SET @myid = NEWID()
SELECT CONVERT(char(255), @myid) AS 'char'
GO

Bei diesem Beispiel wird das aktuelle Datum in das Format 3, tt/mm/jj, konvertiert.

SELECT CONVERT(char(12), GETDATE(), 3)
GO

Konvertieren von datetime- und smalldatetime-Daten

Beim Konvertieren in datetime, lehnt SQL Server 2005 alle Werte ab, die nicht als Daten erkannt werden. Hierzu zählen Daten vor dem 1. Januar 1 n. Chr. Sie können datetime-Werte in smalldatetime konvertieren, wenn das Datum zwischen dem 1. Januar 1900 und dem 6. Juni 2079 liegt. Der Zeitwert wird auf die volle Minute gerundet.

Im folgenden Beispiel werden smalldatetime- und datetime-Werte in varchar- bzw. binary-Datentypen konvertiert.

DECLARE @mydate_sm smalldatetime
SET @mydate_sm = '4/05/98'

SELECT CAST(@mydate_sm AS varchar) AS SM_DATE_VARCHAR
GO

DECLARE @mydate  datetime
SET @mydate     = '4/05/98'

SELECT  CAST(@mydate AS binary) AS DATE_BINARY
GO

Dies ist das Resultset.

(1 row(s) affected)

SM_DATE_VARCHAR                
------------------------------ 
Apr  5 1998 12:00AM            

(1 row(s) affected)

DATE_BINARY                                                    
-------------------------------------------------------------- 
0x0000000000000000000000000000000000000000000000008c3000000000 

(1 row(s) affected)

Konvertieren von float- und real-Daten

Werte des Typs float werden bei der Konvertierung in einen ganzzahligen Datentyp abgeschnitten.

Für das Konvertieren von float- oder real-Daten in Zeichendaten eignet sich die STR-Zeichenfolgenfunktion normalerweise besser als CAST( ). Der Grund hierfür ist, dass STR Ihnen bessere Steuerungsmöglichkeiten über die Formatierung bietet. Weitere Informationen finden Sie unter STR (Transact-SQL) und Funktionen (Transact-SQL).

Konvertieren von money-Daten

Beim Konvertieren von Ganzzahldatentypen in money-Datentypen wird davon ausgegangen, dass es sich bei den Einheiten um Währungseinheiten handelt. Der ganzzahlige Wert 4 entspricht nach der Konvertierung in den money-Datentyp 4 Währungseinheiten.

Im folgenden Beispiel werden smallmoney- und money-Werte in varchar- bzw. decimal-Datentypen konvertiert.

USE AdventureWorks;
GO
DECLARE @mymoney_sm smallmoney;
SET  @mymoney_sm = 3148.29;
SELECT  CAST(@mymoney_sm AS varchar) AS 'SM_MONEY varchar';
GO
DECLARE @mymoney    money;
SET  @mymoney    = 3148.29;
SELECT  CAST(@mymoney AS decimal)    AS 'MONEY DECIMAL';

GO

Dies ist das Resultset.

SM_MONEY VARCHAR               
------------------------------ 
3148.29                        

(1 row(s) affected)

MONEY DECIMAL          
---------------------- 
3148                   

(1 row(s) affected)

Konvertieren von decimal- und numeric-Daten

Im Fall der Datentypen decimal und numeric sieht SQL Server jede auftretende Kombination aus Genauigkeit und Anzahl der Dezimalstellen als einen anderen Datentyp an. decimal(5,5) und decimal(5,0) werden beispielsweise als verschiedene Datentypen erachtet.

In Transact-SQL-Anweisungen wird eine Konstante mit einem Dezimaltrennzeichen automatisch in einen Wert des numeric-Datentyps konvertiert; hierbei werden die mindestens erforderliche Genauigkeit und Anzahl von Dezimalstellen verwendet. Die Konstante 12.345 wird z. B. in einen numeric-Wert mit einer Genauigkeit von 5 und 3 Dezimalstellen konvertiert.

Beim Konvertieren von decimal oder numeric in float oder real kann ein Genauigkeitsverlust auftreten. Beim Konvertieren von int, smallint, tinyint, float, real, money oder smallmoney in decimal oder numeric kann ein Überlauf auftreten.

Bei der Konvertierung einer Zahl in einen Wert des Typs decimal oder numeric mit einer geringeren Genauigkeit und einer geringeren Anzahl von Dezimalstellen wird der Wert standardmäßig von SQL Server gerundet. Wenn allerdings die Option SET ARITHABORT auf ON festgelegt ist, löst SQL Server bei Auftreten eines Überlaufes einen Fehler aus. Eine Verringerung der Genauigkeit und der Anzahl der Dezimalstellen reicht zum Auslösen eines Fehlers nicht aus.

Konvertieren von Datentypen mithilfe von gespeicherten Prozeduren der OLE-Automatisierung

Da SQL Server Transact-SQL-Datentypen und die OLE-Automatisierung Visual Basic-Datentypen verwendet, müssen die gespeicherten Prozeduren der OLE-Automatisierung Daten, die übergeben werden, konvertieren.

In der folgenden Tabelle werden die Konvertierungen von SQL Server in Visual Basic dargestellt.

SQL Server-Datentyp	Visual Basic-Datentyp
char, varchar, text, nvarchar, ntext	String
decimal, numeric	String
bit	Boolean
binary, varbinary, image	Eindimensionales Byte()-Array
int	Long
smallint	Integer
tinyint	Byte
float	Double
real	Single
money, smallmoney	Currency
datetime, smalldatetime	Date
Beliebige auf NULL festgelegte Typen	Variant festgelegt auf NULL

Alle einzelnen SQL Server-Werte mit Ausnahme der binary-, varbinary- undimage-Werte werden in einen einzelnen Visual Basic-Wert konvertiert. Diese Werte werden in ein eindimensionales Byte()-Array in Visual Basic konvertiert. Dieses Array weist einen Bereich von Byte(0 bis length1) auf, wobei length die Anzahl der Byte in den SQL Server binary-, varbinary- oder image-Werten darstellt.

Im Folgenden sehen Sie die Konvertierungen von Visual Basic-Datentypen in SQL Server-Datentypen.

Visual Basic-Datentyp	SQL Server-Datentyp
Long, Integer, Byte, Boolean, Object	int
Double, Single	float
Currency	money
Date	datetime
String mit maximal 4000 Zeichen	varchar/nvarchar
String mit mehr als 4000 Zeichen	text/ntext
Eindimensionales Byte()-Array mit maximal 8000 Byte	varbinary
Eindimensionales Byte()-Array mit mehr als 8000 Byte	image

Siehe auch