CAST et CONVERT (Transact-SQL)

Mis à jour : 17 juillet 2006

Convertit explicitement une expression d'un type de données en une expression d'un type de données différent. CAST et CONVERT offrent la même fonctionnalité.

Conventions de syntaxe de Transact-SQL

Syntaxe

Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Arguments

• expression
  Toute expression valide.

• data_type
  Type de données cible, fourni par le système. Cela comprend xml, bigint et sql_variant. Les données de type alias ne sont pas autorisées. Pour plus d'informations sur les types de données disponibles, consultez Types de données (Transact-SQL).

• length
  Paramètre facultatif de type nchar, nvarchar, char, varchar, binary ou varbinary. Dans le cas de CONVERT, si length n'est pas spécifié, la valeur par défaut est de 30 caractères.

• style
  Style de format de date utilisé pour convertir les données de type datetime ou smalldatetime en données de type caractère (nchar, nvarchar, char, varchar, nchar ou nvarchar), pour convertir les données de type caractère de formats de date ou d'heure connus en données de type datetime ou smalldatetime, ou celui du format de chaîne pour les données de type float, real, money ou smallmoney à convertir en données de type caractère (nchar, nvarchar, char, varchar, nchar ou nvarchar). Si style correspond à NULL, le résultat retourné est également NULL.

  Remarque :

  Les styles répertoriés dans cette rubrique sont pris en charge par SQL Server dans les combinaisons décrites avec les types de données cibles CONVERT. Tous les autres styles et combinaisons ne sont pas pris en charge. N'utilisez pas de style non pris en charge. L'utilisation d'un style non pris en charge ou d'une combinaison de style et de type de données cibles non prise en charge peut renvoyer une erreur ou des résultats non fiables. Ces résultats ne sont pas garantis d'une version à l'autre de SQL Server.

  SQL Server prend en charge le format de date en style arabe, à l'aide de l'algorithme Kuwaiti.

  Dans le tableau ci-après, les deux colonnes de gauche représentent les valeurs style définies pour la conversion de données de type datetime ou smalldatetime en données de type caractère. Ajoutez 100 à une valeur style pour obtenir une année à quatre chiffres incluant le siècle (aaaa).

  Sans siècle (aa) (1)	Avec siècle (aaaa)	Standard	Entrée/Sortie (3)
  -	0 ou 100 (1,2)	Par défaut	mois jj aaaa hh:miAM (ou PM)
  1	101	États-Unis	mm/jj/aaaa
  2	102	ANSI	aa.mm.jj
  3	103	Anglais/Français	jj/mm/aa
  4	104	Allemand	jj.mm.aa
  5	105	Italien	jj-mm-aa
  6	106(1)	-	jj mois aa
  7	107(1)	-	mois jj, aa
  8	108	-	hh:mi:ss
  -	9 ou 109 (1,2)	Valeur par défaut + millièmes de secondes	mois jj aaaa hh:mi:ss:mmmAM (ou PM)
  10	110	USA	mm-jj-aa
  11	111	Japon	aa/mm/jj
  12	112	ISO	aammjj
  -	13 ou 113 (1,2)	Valeur par défaut Europe + millièmes de secondes	jj mois aaaa hh:mi:ss:mmm (24h)
  14	114	-	hh:mi:ss:mmm(24h)
  -	20 ou 120 (2)	ODBC canonique	aaaa-mm-jj hh:mi:ss (24h)
  -	21 ou 121 (2)	ODBC canonique (avec millièmes de secondes)	aaaa-mm-jj hh:mi:ss.mmm (24h)
  -	126 (4)	ISO8601	aaaa-mm-jjThh:mi:ss.mmm (sans espace)
  	127(6, 7)	ISO8601 avec fuseau horaire Z.	jj-mm-aaaaThh:mi:ss.mmmZ (sans espaces)
  -	130 (1,2)	Hijri (5)	jj mois aaaa hh:mi:ss:mmmAM
  -	131 (2)	Hijri (5)	jj/mm/aa hh:mi:ss:mmmAM

  1  Ces valeurs de style retournent des résultats non déterministes. Inclut tous les styles (aa, c'est-à-dire sans siècle) et un sous-ensemble de styles (aaaa, c'est-à-dire avec siècle).

  2 Valeurs par défaut (style0 ou 100, 9 ou 109, 13 ou 113, 20 ou 120, ou encore 21 ou 121) renvoient toujours le siècle (aaaa).

  3  Entrée lors de la conversion en données de type datetime ; ou encore, sortie lors de la conversion en données de type caractère.

  4  Conçue pour le langage XML. Pour la conversion de données datetime ou smalldatetime en données de type caractère, le format de sortie étant celui décrit dans le tableau précédent.

  5 Hijri est un système calendaire possédant de nombreuses variations. SQL Server 2005 utilise l'algorithme Kuwaiti.

  Important :

  Par défaut, SQL Server interprète les années à deux chiffres par rapport à l'année de coupure 2049. C'est-à-dire que l'année 49 est interprétée comme 2049 et l'année 50 comme 1950. De nombreuses applications clientes, comme celles qui s'appuient sur les objets Automation, utilisent l'année de coupure 2030. SQL Server dispose d'une option de configuration (two digit year cutoff) qui permet de modifier l'année de coupure utilisée par SQL Server et garantit ainsi le traitement cohérent des dates. Nous vous recommandons d'utiliser la fonctionnalité d'années exprimées sur quatre chiffres.

  6 Prise en charge uniquement lors de la conversion de données de type caractère en type datetime ou smalldatetime. Lorsque des données de caractères représentant des composants de date ou d'heure uniquement sont converties en type de données datetime ou smalldatetime, le composant d'heure non spécifié est défini à 00:00:00.000, et le composant de date non spécifié est défini à 1900-01-01.

  7 L'indicateur de fuseau horaire facultatif Z facilite le mappage des valeurs datetime XML qui possèdent des informations de fuseau horaire avec les valeurs SQL Server datetime dépourvues d'informations de fuseau horaire. Z est l'indicateur du fuseau horaire UTC-0. Les autres fuseaux horaires sont indiqués avec le décalage HH:MM dans la direction + ou -. Exemple : 2006-12-12T23:45:12-08:00.

  Lorsque vous convertissez des données smalldatetime en données de type caractère, les styles comportant les secondes ou les millièmes de secondes affichent des zéros à ces positions. Si vous convertissez des données de type datetime ou smalldatetime, vous pouvez supprimer les parties non requises de la date en spécifiant la longueur de type de données char ou varchar appropriée.

  Ce tableau suivant illustre les valeurs de style utilisées pour la conversion de données de type float ou real en données de type caractère.

  Valeur	Résultat
  0 (valeur par défaut)	6 chiffres maximum. À utiliser pour la notation scientifique si nécessaire.
  1	Toujours 8 chiffres. À utiliser obligatoirement pour la notation scientifique.
  2	Toujours 16 chiffres. À utiliser obligatoirement pour la notation scientifique.

  Remarque :
  Si le style 126 est spécifié pour la conversion de données float et real, la sortie revient à une valeur de style de 2 et 1, respectivement.

  Ce tableau suivant illustre les valeurs de style utilisées pour la conversion de données de type money ou smallmoney en données de type caractère.

  Valeur	Résultat
  0 (valeur par défaut)	Pas de virgule tous les trois chiffres à gauche du point décimal, et deux chiffres à droite de celui-ci. Par exemple, 4235.98.
  1	Insertion d'une virgule tous les trois chiffres à gauche du point décimal, et deux chiffres à droite de celui-ci. Par exemple 3,510.92.
  2	Pas de virgule tous les trois chiffres à gauche du point décimal, et quatre chiffres à droite de celui-ci. Par exemple 4235.9819.

  Remarque :
  Si le style 126 est spécifié pour la conversion de données money ou smallmoney en données de type caractère, la sortie revient à une valeur de style de 2.

  Ce tableau suivant illustre les valeurs de style utilisées pour la conversion d'entrée de chaîne en données de type xml.

  Valeur

Notes

Une conversion implicite est une conversion pour laquelle il n'est pas nécessaire de spécifier les fonctions CAST ou CONVERT. À l'inverse, une conversion explicite requiert que la fonction CAST ou CONVERT soit indiquée. L'illustration suivante reprend toutes les conversions de types de données explicites et implicites autorisées pour les types de données SQL Server 2005 fournis par le système. Ceux-ci comprennent notamment xml, bigint et sql_variant. Aucune conversion implicite d'attribution de valeur n'est effectuée à partir du type de données sql_variant, mais une conversion implicite vers sql_variant existe.

Remarque :
Les données Unicode utilisent toujours un nombre pair d'octets et vous devez par conséquent faire attention lorsque vous convertissez des données de type binary ou varbinary vers ou à partir des types de données pris en charge par Unicode. Ainsi, la conversion suivante retourne la valeur hexadécimale 4100 et non pas 41 : SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary).

Types de données de valeur élevée

Les types de données correspondant à des valeurs élevées font preuve du même comportement de conversion, à la fois implicite et explicite, que leur contreparties de poids plus faible, plus particulièrement en ce qui concerne les types varchar, nvarchar et varbinary. Vous devez cependant prendre en compte les recommandations suivantes :

• La conversion du type image en varbinary(max) et vice versa est une conversion implicite ; il en va de même pour la conversion entre text et varchar(max) d'une part et ntext et nvarchar(max) d'autre part.
• La conversion de types de données de valeurs élevées, comme c'est le cas pour le type varchar(max), en type équivalent mais de poids plus faible, par exemple varchar, reste aussi une conversion implicite, mais la valeur trop grande risque d'être tronquée si elle dépasse la longueur indiquée autorisée pour le type de données de poids dont l'espace mémoire alloué reste plus modeste.
• Les conversions de varchar, de nvarchar ou de varbinary à leur équivalent plus volumineux en espace mémoire sont également opérées de façon implicite.
• Enfin, les conversions du type de données sql_variant en type plus conséquent correspondent cependant à une conversion explicite.
• Il reste à savoir que les types de données de valeurs élevées ne peuvent pas être convertis en sql_variant.

Pour plus d'informations sur la conversion de types personnalisés créés par le biais du Microsoft .NET Framework common language runtime (CLR), consultez Exécution d'opérations sur des types définis par l'utilisateur. Pour plus d'informations sur la conversion à partir du type de données xml, consultez Génération d'instances XML.

Type de données xml

Lors de la conversion explicite ou implicite du type de données xml en type de données de chaîne ou de caractères, le contenu du type de données xml est sérialisé par rapport à un ensemble de règles. Pour plus d'informations sur ces règles, consultez Sérialisation des données XML. Pour plus d'informations sur la conversion de XML en type CLR défini par l'utilisateur, consultez Exécution d'opérations sur des types définis par l'utilisateur. Pour plus d'informations sur la conversion d'autres types de données au type de données xml, consultez Génération d'instances XML.

Types de données text et image

Les types de données text et image ne prennent pas en charge la conversion automatique de types de données. Vous pouvez explicitement convertir des données de type text en type caractère et des données de type image en binary ou en varbinary, mais la longueur maximale admise est de 8 000 octets. Si vous tentez une conversion non prévue, par exemple une expression de type caractère incluant des lettres et convertie en type int, SQL Server retourne alors un message d'erreur.

Classement des résultats

Lorsque l'entrée et la sortie de CAST ou CONVERT sont des chaînes de caractères, l'entrée et la sortie présentent les mêmes classement et étiquette de classement. Si l'entrée n'est pas une chaîne de caractères, la sortie présente le classement par défaut de la base de données et une étiquette de classement de contrainte par défaut. Pour plus d'informations, consultez Priorité de classement (Transact-SQL).

Pour attribuer un classement différent à la sortie, appliquez la clause COLLATE à l'expression de résultat de la fonction CAST ou CONVERT. Exemple :

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

Troncation et arrondi des résultats

Lors de la conversion d'une expression de type binaire ou caractère (char, nchar, nvarchar, varchar, binary ou varbinary) en une expression d'un type différent, les données peuvent être tronquées ou partiellement affichées, ou une erreur est retournée si le résultat est trop court pour pouvoir être affiché. Une expression convertie en donnée de type char, varchar, nchar, nvarchar, binary et varbinary est par conséquent tronquée, sauf pour les conversions répertoriées dans le tableau suivant.

* = Expression résultante trop courte pour être affichée. E = Erreur retournée parce que l'expression résultante est trop courte pour être affichée.

SQL Server garantit que seules des conversions en boucle, c'est-à-dire qui convertissent un type de données en un autre puis à nouveau en son type d'origine, restitueront les mêmes valeurs d'une version à l'autre de l'application. L'exemple suivant illustre un tel cas de figure :

DECLARE @myval decimal (5, 2)
SET @myval = 193.57
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))

Remarque :
Ne tentez pas d'élaborer des valeurs de type binary destinées à une conversion dans un type de la catégorie numérique. SQL Server ne peut en effet pas assurer que des données de type decimal ou numeric converties en binary reviendront au même entre les différentes versions de SQL Server.

L'exemple suivant illustre une expression résultante trop petite pour être affichée.

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, SUBSTRING(c.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave'
FROM HumanResources.Employee e JOIN Person.Contact c ON e.EmployeeID = c. ContactID
WHERE NOT EmployeeID >5

Voici l'ensemble des résultats.

FirstName      LastName         Title                  Sick Leave
---------      ---------      -------------------   -----------
Gustavo        Achong         Mr.                   *
Catherine      Abel           Ms.                   *
Kim            Abercrombie    Ms.                   *
Humberto       Acevedo        Sr.                   *
Pilar          Ackerman       Sra.                  *

(5 row(s) affected)

Si vous convertissez des données dont le type diffère sur l'emplacement de la virgule, la valeur résultante peut parfois être tronquée, parfois être arrondie. Le tableau suivant illustre ce comportement.

Par exemple, le résultat découlant de la conversion suivante est 10 :

SELECT CAST(10.6496 AS int)

Lors des conversions entre divers types de données dans lesquelles les types de données cibles comptent moins de chiffres décimaux que les types de données sources, la valeur obtenue est arrondie. Par exemple, le résultat découlant de la conversion suivante est $10.3497 :

SELECT CAST(10.3496847 AS money)

SQL Server retourne un message d'erreur lorsque des données de type char, nchar, varchar ou nvarchar sont converties en données int, float, numeric ou decimal, ou lorsqu'une chaîne vide (« ») est convertie en données de type numeric ou decimal.

Conversion de données de type chaîne binaire

Lorsque des données de type binary ou varbinary sont converties en données de type caractère et qu'un nombre impair de valeurs est spécifié après le x, SQL Server ajoute un 0 (zéro) après le x afin de produire un nombre pair de valeurs.

Les données binaires se composent de caractères, de 0 à 9 et de A à F (ou de a à f), regroupés par deux. Les chaînes binaires doivent être précédées de 0x. Par exemple, pour obtenir FF, entrez 0xFF. La valeur maximale est une valeur binaire occupant 8 000 octets, chacun étant FF. Les types de données binary conviennent pour des formats binaires plutôt que pour des données hexadécimales. Les conversions et les calculs portant sur les nombres hexadécimaux stockés sous forme de données binaires peuvent ne pas être fiables.

Lors de la définition de la longueur d'un type de données binary, chaque groupe de deux caractères compte comme un seul caractère. Une longueur égale à 10 signifie que 10 groupes de deux caractères seront entrés.

Les chaînes binaires vides, représentées par 0x, peuvent être stockées sous forme de données binaires.

Types de retour

Retourne la même valeur que data_type.

Exemples

A. Utilisation simultanée de CAST et CONVERT

Chacun des exemples extrait le nom de chaque produit possédant un 3 au premier chiffre de son prix et convertit son champ ListPrice en type int.

-- Use CAST
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT.
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO

B. Utilisation de CAST avec des opérateurs arithmétiques

L'exemple suivant illustre le calcul effectué sur une colonne unique (intitulée Computed) où le total des ventes de l'année en cours (SalesYTD) est divisé par le pourcentage de commission (dont la valeur se trouve dans CommissionPCT). Le résultat est converti en données de type int après avoir été arrondi au chiffre entier le plus proche.

USE AdventureWorks;
GO
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS 'Computed'
FROM Sales.SalesPerson 
WHERE CommissionPCT != 0;
GO

Voici l'ensemble des résultats.

Computed      
------ 
379753754
346698349
257144242
176493899
281101272
0
301872549
212623750
298948202
250784119
239246890
101664220
124511336
97688107

(14 row(s) affected)

C. Utilisation de CAST pour la concaténation d'expressions

L'exemple suivant illustre la concaténation d'expressions de type non caractère et non binaire par le biais de CAST.

USE AdventureWorks;
GO
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 350.00 AND 400.00;
GO

Voici l'ensemble des résultats.

ListPrice
------------------
The list price is 357.06
The list price is 364.09
The list price is 364.09
The list price is 364.09
The list price is 364.09

(5 row(s) affected)

D. Utilisation de CAST pour faciliter la lecture des résultats

L'exemple suivant s'appuie sur CAST dans la liste de sélection afin de convertir la colonne Name en colonne de type char(10).

USE AdventureWorks;
GO
SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice
FROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductID
WHERE Name LIKE 'Long-Sleeve Logo Jersey, M';
GO

Voici l'ensemble des résultats.

Name       UnitPrice
---------- ---------------------
Long-Sleev 31.2437
Long-Sleev 32.4935
Long-Sleev 49.99

(3 row(s) affected)

E. Utilisation de CAST avec la clause LIKE

L'exemple suivant convertit la colonne SalesYTD de type money en colonne de type int, puis en colonne de type char(20) de façon à pouvoir l'utiliser avec la clause LIKE.

USE AdventureWorks;
GO
SELECT p.FirstName, p.LastName, s.SalesYTD, s.SalesPersonID
FROM Person.Contact p JOIN Sales.SalesPerson s ON p.ContactID = s.SalesPersonID
WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO

Voici l'ensemble des résultats.

FirstName        LastName            SalesYTD         SalesPersonID
---------------- ------------------- ---------------- -------------
Carol            Elliott             2811012.7151      279
Julie            Estes               219088.8836       288
Janeth           Esteves             2241204.0424      289

(3 row(s) affected)

F. Utilisation de CONVERT ou de CAST avec des données au format XML typé

Voici une série d'exemples illustrant l'utilisation de la fonction CONVERT pour convertir des données en XML typé en utilisant le type de données XML.

Cet exemple convertit une chaîne incluant des espaces, du texte et des balises en XML typé, puis supprime tous les espaces non significatifs (correspondant aux espaces délimitant les nœuds) :

CONVERT(XML, '<root><child/></root>')

Cet exemple convertit une chaîne similaire incluant des espaces, du texte et des balises en XML typé, mais conserve les espaces non significatifs (correspondant aux espaces délimitant les nœuds) :

CONVERT(XML, '<root>          <child/>         </root>', 1)

Enfin, cet exemple convertit une chaîne similaire incluant des espaces, du texte et des balises en XML typé :

CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

Pour obtenir plus d'exemples, consultez Génération d'instances XML.

G. Utilisation de CAST et de CONVERT avec des données de type datetime

L'exemple suivant affiche la date et l'heure actuelles, utilise CAST pour convertir la date et l'heure actuelles en données de type caractère, puis utilise CONVERT pour afficher la date et l'heure au format ISO 8901.

SELECT 
   GETDATE() AS UnconvertedDateTime,
   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
   CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;
GO

Voici l'ensemble des résultats.

UnconvertedDateTime     UsingCast                      UsingConvertTo_ISO8601

----------------------- ------------------------------ ------------------------------

2006-04-18 09:58:04.570 Apr 18 2006 9:58AM            2006-04-18T09:58:04.570

(1 row(s) affected)

L'exemple suivant est plus ou moins l'inverse de l'exemple précédent. Cet exemple affiche la date et l'heure sous forme de données de type caractère, utilise CAST pour convertir les données caractère en données de type datetime, puis utilise CONVERT pour convertir les données caractère en données de type datetime.

SELECT 
   '2006-04-04T15:50:59.997' AS UnconvertedText,
   CAST('2006-04-04T15:50:59.997' AS datetime) AS UsingCast,
   CONVERT(datetime, '2006-04-04T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;
GO

Voici l'ensemble des résultats.

UnconvertedText         UsingCast               UsingConvertFrom_ISO8601

----------------------- ----------------------- ------------------------

2006-04-04T15:50:59.997 2006-04-04 15:50:59.997 2006-04-04 15:50:59.997

(1 row(s) affected)

Voir aussi

Référence

SELECT (Transact-SQL)
Fonctions système (Transact-SQL)

Autres ressources

Conversion de types de données (moteur de base de données)
Format ISO 8601
Rédaction d'instructions Transact-SQL internationales

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications