Conversion de types de données (moteur de base de données)
Les types de données peuvent être convertis dans les scénarios suivants :
Lorsqu'elles sont déplacées dans, comparées à ou combinées avec les données d'un autre objet, les données d'un objet doivent être converties du type de données d'un objet en type de données de l'autre.
Lorsque les données d'une colonne de résultats, d'un code de retour ou d'un paramètre de sortie Transact-SQL sont déplacées dans une variable de programme, elles doivent être converties du type de données système SQL Server vers le type de données de la variable.
Les types de données peuvent être convertis implicitement ou explicitement :
Les conversions implicites sont invisibles pour l'utilisateur.
SQL Server convertit automatiquement les données d'un type de données en un autre. Par exemple, si un smallint est comparé à un int, le smallint est implicitement converti en int avant que la comparaison soit réalisée.
Les conversions explicites utilisent les fonctions CAST ou CONVERT.
Les fonctions CAST et CONVERT convertissent une valeur (une variable locale, une colonne ou une autre expression) d'un type de données en un autre. Par exemple, la fonction CAST convertit la valeur numérique de $157.27 en une chaîne de caractères '157.27' :
CAST ( $157.27 AS VARCHAR(10) )Utilisez CAST au lieu de CONVERT si vous souhaitez que le code de programmation Transact-SQL soit compatible avec la norme ISO. Utilisez la fonction CONVERT et non la fonction CAST pour bénéficier de la fonctionnalité style de la fonction CONVERT.
Lors de la conversion du type de données d'un objet SQL Server en un autre, certaines conversions de types de données implicites et explicites ne sont pas prises en charge. Ainsi, une valeur nchar ne peut pas être convertie en valeur image. Un nchar ne peut être converti en binary qu'en utilisant une conversion explicite, une conversion implicite en binary n'est pas acceptée. Cependant, un nchar peut être converti explicitement ou implicitement en nvarchar.
Lors de la manipulation de types de données sql_variant, SQL Server prend en charge les conversions implicites en type sql_variant d'objets de type de données différent. Cependant, SQL Server ne prend pas en charge les conversions implicites de données sql_variant en un objet possédant un autre type de données.
Pour plus d'informations sur les conversions prises en charge entre des objets SQL Server, consultez CAST et CONVERT (Transact-SQL).
Lors de la conversion entre une variable d'application et une colonne de l'ensemble de résultats, un code de retour, un paramètre ou un marqueur de paramètre SQL Server, les conversions de types de données acceptées sont définies par l'API de base de données. Pour plus d'informations, consultez Déplacement des données dans des variables de programme.
Comportements de conversion de types de données
Les sections suivantes de cette rubrique décrivent les comportements de conversion propres aux types de données suivants :
Données binary et varbinary |
Données money (monétaires) |
Données bit |
Données decimal et numeric (décimales et numériques) |
Données de caractères |
Conversion de types de données à l'aide des procédures stockées OLE Automation |
Données de date et d'heure |
Données integer |
Données float et real |
Conversion des données binary et varbinary
Lorsque les données sont converties d'un type de données chaîne (char, varchar, nchar, nvarchar, binary, varbinary, text, ntext ou image) en un type de données binary ou varbinary de longueur différente, SQL Server complète ou tronque la chaîne à droite. Lorsque d'autres types de données sont convertis en binary ou varbinary, les données sont complétées ou tronquées à gauche. Elles sont complétées avec des zéros hexadécimaux.
La conversion de données en types de données binary et varbinary peut être utile si les données binary constituent le moyen de déplacement de données le plus pratique. La conversion d'une valeur d'un type quelconque en valeur binaire de taille suffisante, puis sa reconversion en son type d'origine produisent toujours la même valeur si les deux conversions sont effectuées dans la même version de SQL Server. La représentation binaire d'une valeur peut varier d'une version à l'autre de SQL Server.
Vous pouvez convertir int, smallint et tinyint en binary ou varbinary, mais si vous reconvertissez la valeur binary en valeur entière, elle sera différente de la valeur entière initiale s'il y a eu troncation. Par exemple, l'instruction SELECT suivante montre que la valeur entière 123456 est généralement stockée sous forme binaire 0x0001e240 :
SELECT CAST( 123456 AS BINARY(4) )
Cependant, l'instruction SELECT suivante montre que si la cible binary est trop petite pour traiter la valeur entière, les chiffres les plus à gauche sont tronqués discrètement de façon à ce que le même nombre soit stocké en tant que 0xe240 :
SELECT CAST( 123456 AS BINARY(2) )
L'instruction suivante montre que la troncation discrète peut affecter les opérations arithmétiques sans engendrer d'erreur :
DECLARE @BinaryVariable2 BINARY(2)
SET @BinaryVariable2 = 123456
SET @BinaryVariable2 = @BinaryVariable2 + 1
SELECT CAST( @BinaryVariable2 AS INT)
GO
Le résultat final est 57921, et non pas 123457.
[!REMARQUE]
Les conversions entre un type de données quelconque et les types de données binary ne sont pas nécessairement identiques dans toutes les versions de SQL Server.
Conversion en données binaires
Lors d'une conversion en bit, toute valeur différente de zéro est changée en 1.
Conversion des données de type caractère
Lorsque des expressions de caractères sont converties en type caractère de taille différente, les valeurs trop longues pour le nouveau type de données sont tronquées.
Lorsqu'une expression de caractères est convertie en une expression de caractères de taille ou de type de données différente (par exemple, de char(5) à varchar(5) ou de char(20) à char(15)), le classement de la valeur d'entrée est affecté à la valeur convertie. Si une expression de type non caractère est convertie en un type de données caractère, le classement par défaut de la base de données active est affecté à la valeur convertie. Dans les deux cas, vous pouvez affecter un classement spécifique à l'aide de la clause COLLATE.
[!REMARQUE]
Les interprétations de page de codes sont prises en charge pour les types de données char et varchar, mais pas pour text. De manière identique aux versions antérieures de SQL Server, la perte de données lors de l'interprétation d'une page de codes n'est pas mentionnée.
Les expressions de caractères converties en un type de données numeric approximatif peuvent éventuellement inclure une indication exponentielle (un « e » majuscule ou minuscule éventuellement suivi d'un plus (+) ou d'un moins (-) et du nombre).
Les expressions de caractères converties en un type de données numeric exact doivent être composées de chiffres, d'un point (séparateur décimal en notation anglaise) et éventuellement d'un plus (+) ou d'un moins (-). Les espaces à gauche sont ignorés. Les séparateurs virgule (séparateurs de milliers en notation anglaise comme dans 123,456.00) ne sont pas autorisés dans la chaîne.
Les expressions de caractères converties en types de données money ou smallmoney peuvent également comprendre un point facultatif (séparateur décimal en notation anglaise) et le symbole du dollar ($). Les séparateurs virgule (séparateurs de milliers en notation anglaise comme dans $123,456.00) sont autorisés.
L'exemple suivant montre comment convertir des données pour l'affichage. L'exemple convertit les données des ventes en données de type caractères avant de réaliser une comparaison de chaîne et convertit la date actuelle en style 3, dd/mm/yy.
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
L'exemple suivant convertit une valeur uniqueidentifier en un type de données char.
DECLARE @myid uniqueidentifier
SET @myid = NEWID()
SELECT CONVERT(char(255), @myid) AS 'char'
GO
Cet exemple convertit la date actuelle en style 3, dd/mm/yy.
SELECT CONVERT(char(12), GETDATE(), 3)
GO
Conversion de données de date et d'heure
Lorsque vous effectuez une conversion vers des types de données date et time , SQL Server rejette toutes les valeurs qu'il ne peut identifier comme dates ou heures. Pour obtenir une vue d'ensemble de tous les types de données et fonctions de date et d'heure Transact-SQL, consultez Fonctions de date et d'heure (Générateur de rapports version 1.0).
L'exemple suivant convertit respectivement les valeurs date et datetime2 en types de données varchar et binary.
DECLARE @mydate date
SET @mydate = '4/05/98'
SELECT CAST(@mydate AS varchar) AS DATE_VARCHAR
GO
DECLARE @mydate datetime2
SET @mydate = '4/05/98'
SELECT CAST(@mydate AS binary) AS DATE_BINARY
GO
Voici l'ensemble des résultats.
(1 row(s) affected)
DATE_VARCHAR
------------------------------
Apr 5 1998
(1 row(s) affected)
DATE_BINARY
--------------------------------------------------------------
0x0700000000008B210B
(1 row(s) affected)
Conversion de données float et real
Les valeurs de float sont tronquées lorsqu'elles sont converties en un type de données entier.
Si vous souhaitez effectuer une conversion de float ou real en données caractères, la fonction de chaîne STR constitue généralement un meilleur choix que CAST( ), car STR permet un plus grand contrôle sur le format. Pour plus d'informations, consultez STR (Transact-SQL) et Fonctions (Transact-SQL).
Conversion des données monétaires
Lors de la conversion en money du type de données entiers, les unités sont supposées être des unités monétaires. Par exemple, la valeur entière 4 est convertie par l'équivalent en money de 4 unités monétaires.
L'exemple suivant convertit les valeurs smallmoney et money respectivement en types de données varchar en decimal.
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
Voici l'ensemble des résultats.
SM_MONEY VARCHAR
------------------------------
3148.29
(1 row(s) affected)
MONEY DECIMAL
----------------------
3148
(1 row(s) affected)
Conversion des données numériques et décimales
Pour les types de données decimal et numeric, SQL Server considère chaque combinaison de précision et d'échelle comme un type de données différent. Par exemple, decimal(5,5) et decimal(5,0) sont considérés comme des types de données différents.
Dans les instructions Transact-SQL, une constante avec une virgule est automatiquement convertie en valeur de donnée numeric, en utilisant la précision et l'échelle requises minimales. Par exemple, la constante 12,345 est convertie en valeur numeric avec une précision de 5 et une échelle de 3.
La conversion de decimal ou de numeric en float ou real peut entraîner une certaine perte de précision. La conversion de int, smallint, tinyint, float, real, money ou smallmoney en decimal ou numeric peut entraîner un dépassement de capacité.
Par défaut, SQL Server utilise les arrondis lorsqu'il convertit un nombre en valeur decimal ou numeric avec une précision et une échelle plus faibles. Toutefois, si l'option SET ARITHABORT a la valeur ON, SQL Server déclenche une erreur lors du dépassement de capacité. Perdre uniquement la précision et l'échelle ne suffit pas à engendrer une erreur.
Conversion de données integer
Lorsque les entiers sont convertis implicitement en un type de données character, si l'entier est trop grand pour le champ de caractère, SQL Server affiche le caractère ASCII 42, l'astérisque (*).
Les constantes entières supérieures à 2 147 483 647 sont converties en type de données decimal, et non bigint. L'exemple suivant montre que lorsque la valeur de seuil est dépassée, le type de données du résultat devient decimal au lieu de int.
SELECT 2147483647 / 2 AS Result1, 2147483649 / 2 AS Result2 ;
Voici l'ensemble des résultats.
Result1 Result2
1073741823 1073741824.500000
Conversion de types de données à l'aide des procédures stockées OLE Automation
Étant donné que SQL Server utilise les types de données Transact-SQL et que OLE Automation utilise les types de données Visual Basic, les procédures stockées OLE Automation doivent convertir les données qu'elles s'échangent.
Le tableau suivant décrit la conversion des types de données SQL Server en types de données Visual Basic.
Type de données SQL Server |
Type de données Visual Basic |
|---|---|
char, varchar, text, nvarchar, ntext |
String |
decimal, numeric |
String |
bit |
Boolean |
binary, varbinary, image |
Tableau Byte() unidimensionnel |
int |
Long |
smallint |
Integer |
tinyint |
Byte |
float |
Double |
real |
Single |
money, smallmoney |
Currency |
datetime, smalldatetime |
Date |
Tout type qui prend la valeur NULL |
Variant ayant la valeur Null |
Toutes les valeurs uniques de SQL Server sont converties en une valeur unique Visual Basic, à l'exception des valeurs binary, varbinary et image. Ces valeurs sont converties en tableau d'octets Byte() à une dimension dans Visual Basic. Ce tableau possède une plage de Byte(0 à length1), où length représente le nombre d'octets dans les valeurs binary, varbinary ou image de SQL Server.
Il s'agit des conversions des types de données Visual Basic en types de données SQL Server.
Type de données Visual Basic |
Type de données SQL Server |
|---|---|
Long, Integer, Byte, Boolean, Object |
int |
Double, Single |
float |
Currency |
money |
Date |
datetime |
String de 4 000 caractères maximum |
varchar/nvarchar |
String de plus de 4 000 caractères |
text/ntext |
Tableau de type Byte() à une dimension de 8 000 octets maximum |
varbinary |
Tableau de type Byte() à une dimension de plus de 8 000 octets |
image |