CAST et CONVERT (Transact-SQL)
S’applique à : 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
Ces fonctions convertissent une expression d’un type de données en un autre.
Syntaxe
Syntaxe CAST :
CAST ( expression AS data_type [ ( length ) ] )
Syntaxe CONVERT :
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Conventions de la syntaxe Transact-SQL
Arguments
expression
Toute expression valide.
data_type
Type de données cible. Exemples : xml, bigint et sql_variant. Les types de données alias ne sont pas autorisés.
length
Entier facultatif qui spécifie la longueur du type de données cible pour les types de données qui autorisent une longueur spécifiée par l’utilisateur. La valeur par défaut est 30.
style
Expression d’entier qui spécifie comment la fonction CONVERT doit traduire expression. Si le style est NULL, une valeur NULL est retournée. La plage est déterminée par data_type.
Types de retour
Retourne expression traduite en data_type.
Styles de date et d’heure
Quand expression est un type de données de date ou d’heure, style peut prendre l’une des valeurs indiquées dans le tableau suivant. Les autres valeurs sont traitées comme étant 0. À partir de SQL Server 2012 (11.x), les seuls styles pris en charge lors de la conversion des types de date et d’heure en datetimeoffset sont 0 ou 1. Tous les autres styles de conversion retournent l'erreur 9809.
Notes
SQL Server prend en charge le format de date dans le style arabe à l’aide de l’algorithme koweitien.
| Sans siècle (aa) 1 | Avec siècle (aaaa) | standard | Entrée/sortie 3 |
|---|---|---|---|
| - | 0 ou 100 1, 2 | Valeur par défaut pour datetime et smalldatetime | mon dd yyyy hh:miAM (ou PM) |
| 1 | 101 | États-Unis | 1 = mm/dd/yy101 = mm/dd/yyyy |
| 2 | 102 | ANSI | 2 = yy.mm.dd102 = yyyy.mm.dd |
| 3 | 103 | Anglais/Français | 3 = dd/mm/yy103 = dd/mm/yyyy |
| 4 | 104 | Allemand | 4 = dd.mm.yy104 = dd.mm.yyyy |
| 5 | 105 | Italien | 5 = dd-mm-yy105 = dd-mm-yyyy |
| 6 | 106 1 | - | 6 = dd mon yy106 = dd mon yyyy |
| 7 | 107 1 | - | 7 = Mon dd, yy107 = Mon dd, yyyy |
| 8 ou 24 | 108 | - | hh:mi:ss |
| - | 9 ou 109 1, 2 | Valeur par défaut + millièmes de secondes | mon dd yyyy hh:mi:ss:mmmAM (ou PM) |
| 10 | 110 | États-Unis | 10 = mm-jj-aa 110 = mm-dd-yyyy |
| 11 | 111 | JAPON | 11 = aa/mm/jj 111 = yyyy/mm/dd |
| 12 | 112 | ISO | 12 = aammjj 112 = yyyymmdd |
| - | 13 ou 113 1, 2 | Valeur par défaut Europe + millièmes de secondes | dd mon yyyy hh:mi:ss:mmm (24 heures) |
| 14 | 114 | - | hh:mi:ss:mmm (24 heures) |
| - | 20 ou 120 2 | ODBC canonique | yyyy-mm-dd hh:mi:ss (24 heures) |
| - | 21 ou 25 ou 121 2 | Valeur canonique ODBC par défaut (avec millisecondes) pour time, date, datetime2 et datetimeoffset | yyyy-mm-dd hh:mi:ss.mmm (24 heures) |
| 22 | - | États-Unis | mm/dd/yy hh:mi:ss AM (ou PM) |
| - | 23 | ISO8601 | yyyy-mm-dd |
| - | 126 4 | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm (sans espace) 6 |
| - | 127 8, 9 | ISO8601 avec fuseau horaire Z | yyyy-MM-ddThh:mm:ss.fffZ (sans espace) 6 |
| - | 130 1, 2 | Hijri (5) | dd mon yyyy hh:mi:ss:mmmAM7 |
| - | 131 2 | Hijri (5) | dd/mm/yyyy hh:mi:ss:mmmAM |
1 Ces valeurs de style retournent des résultats non déterministes. Inclut tous les styles (yy) (sans siècle) et un sous-ensemble de styles (yyyy) (avec siècle).
2 Les valeurs par défaut (0 ou 100, 9 ou 109, 13 ou 113, 20 ou 120, 23 et 21 ou 25 or 121) retournent toujours le siècle (yyyy).
Important
Par défaut, SQL Server interprète les années à deux chiffres par rapport à l'année de coupure 2049. Autrement dit, SQL Server interprète l’année à deux chiffres 49 comme étant 2049 et l’année 50 comme étant 1950. De nombreuses applications clientes, comme celles basées sur les objets Automation, utilisent l’année de coupure 2030. SQL Server fournit l’option de configuration Année de coupure à deux chiffres permettant de modifier l’année de coupure utilisée par SQL Server. Cela permet de traiter les dates de manière cohérente. Nous vous recommandons d'utiliser la fonctionnalité d'années exprimées sur quatre chiffres.
3 Entrée lors de la conversion en données de type datetime ; ou encore, sortie lors de la conversion en données caractères.
4 Conçue pour le langage XML. Pour la conversion de données datetime ou smalldatetime en données caractères, le format de sortie est décrit dans le tableau précédent.
5 Hijri est un système de calendrier avec différentes variantes. SQL Server utilise l'algorithme koweitien.
6 Pour une valeur en millisecondes (mmm) égale à 0, la valeur de la fraction décimale n’est pas affichée. Par exemple, la valeur 2022-11-07T18:26:20.000 s’affiche sous la forme 2022-11-07T18:26:20.
7 Dans ce style, mon correspond à une représentation Unicode Hijri à plusieurs jetons du nom complet du mois. Cette valeur n’est pas retournée correctement sur les installations en anglais américain par défaut de SSMS.
8 Prise en charge uniquement lors de la conversion de données caractères en datetime ou smalldatetime. Lors de la conversion de données caractères représentant des composants de date ou d’heure uniquement en type de données datetime ou smalldatetime, le composant d’heure non spécifié est défini sur 00:00:00.000 et le composant de date non spécifié est défini sur 1900-01-01.
9 L’indicateur de fuseau horaire facultatif Z facilite le mappage des valeurs datetime XML comprenant des informations de fuseau horaire aux valeurs datetime SQL Server dépourvues d’informations de fuseau horaire. Z indique le fuseau horaire à UTC-0. Le décalage HH:MM, dans la direction + ou -, indique d’autres fuseaux horaires. Par exemple : 2022-12-12T23:45:12-08:00.
Quand vous convertissez des données smalldatetime en données caractères, les styles comportant des secondes ou des millisecondes affichent des zéros à ces positions. Quand vous convertissez des valeurs datetime ou smalldatetime, utilisez une longueur de type de données char ou varchar appropriée pour tronquer les parties de date inutiles.
Quand vous convertissez des données caractères en données datetimeoffset avec un style qui inclut une heure, un décalage de fuseau horaire est ajouté au résultat.
Styles float et real
Quand expression est un type float ou real, style peut prendre l’une des valeurs indiquées dans le tableau suivant. Les autres valeurs sont traitées comme étant 0.
| Valeur | Output |
|---|---|
| 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. |
| 3 | Toujours 17 chiffres. À utiliser pour la conversion sans perte. Avec ce style, la conversion de chaque valeur float ou real distincte en chaîne de caractères distincte est garantie. S’applique à : SQL Server 2016 (13.x) et versions ultérieures, et Azure SQL Database. |
| 126, 128, 129 | Inclus pour des raisons d’héritage. N’utilisez pas ces valeurs pour un nouveau développement. |
Styles money et smallmoney
Quand expression est un type money ou smallmoney, style peut prendre l’une des valeurs indiquées dans le tableau suivant. Les autres valeurs sont traitées comme étant 0.
| Valeur | Output |
|---|---|
| 0 (valeur par défaut) | Pas de virgule tous les trois chiffres à gauche du point décimal, et deux chiffres à droite de celui-ci. Exemple : 4235.98. |
| 1 | Insertion d’une virgule tous les trois chiffres à gauche du point décimal, et deux chiffres à droite de celui-ci. Exemple : 3,510.92. |
| 2 | Pas de virgule tous les trois chiffres à gauche du point décimal, et quatre chiffres à droite de celui-ci. Exemple : 4235.9819. |
| 126 | Équivalent du style 2 lors de la conversion en char(n) ou varchar(n) |
styles xml
Quand expression est un type xml, style peut prendre l’une des valeurs indiquées dans le tableau suivant. Les autres valeurs sont traitées comme étant 0.
| Valeur | Output |
|---|---|
| 0 (valeur par défaut) | Utiliser le comportement d’analyse par défaut permettant de supprimer les espaces non significatifs et n’autorisant pas de sous-ensemble DTD interne. Remarque : Quand vous effectuez une conversion vers le type de données xml, les espaces non significatifs de SQL Server sont gérés différemment de ceux dans XML 1.0. Pour plus d’informations, consultez Créer des instances de données XML. |
| 1 | Maintien des espaces non significatifs. Ce paramètre de style définit la gestion par défaut de xml:space pour correspondre au comportement de xml:space="preserve" . |
| 2 | Activation du traitement limité de sous-ensembles DTD internes. Si cette option est activée, le serveur peut utiliser les informations suivantes fournies dans un sous-ensemble DTD interne afin de procéder à des opérations d’analyse autres que celles de validation des données. - Les valeurs par défaut concernant les attributs sont alors appliquées - Les références à des entités internes sont résolues et développées - Le modèle de contenu DTD fait ensuite l’objet d’une vérification syntaxique L’analyseur ignore les sous-ensembles DTD externes. Il n’évalue pas non plus la déclaration des balises XML au niveau de l’attribut standalone pour vérifier s’il a la valeur yes ou no. Il analyse cependant l’instance XML comme s’il s’agissait d’un document autonome. |
| 3 | Maintien des espaces non significatifs et activation du traitement limité de sous-ensembles DTD internes. |
Styles binaires
Pour une expression binary(n), char(n), varbinary(n)ou varchar(n), le style peut avoir l’une des valeurs indiquées dans le tableau suivant. Les valeurs de style qui ne sont pas répertoriées dans le tableau retournent une erreur.
| Valeur | Output |
|---|---|
| 0 (valeur par défaut) | Traduit des caractères ASCII en octets binaires ou des octets binaires en caractères ASCII. Chaque caractère ou octet est converti 1:1. Si data_type est un type binaire, les caractères 0x sont ajoutés à gauche du résultat. |
| 1, 2 | Si data_type est un type binaire, l’expression doit être une expression de caractères. L’expression doit être composée d’un nombre pair de chiffres hexadécimaux (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). Si style a la valeur 1, les caractères 0x doivent être les deux premiers caractères de l’expression. Si l’expression contient un nombre impair de caractères ou si l’un des caractères n’est pas valide, une erreur est générée. Si la longueur de l’expression convertie dépasse la longueur de data_type, le résultat est tronqué à droite. Les data_type de longueur fixe qui sont plus longs que le résultat converti se voient ajouter des zéros à droite du résultat. Un data_type de type caractère nécessite une expression binaire. Chaque caractère binaire est converti en deux caractères hexadécimaux. Supposons que la longueur de l’expression convertie dépasse la longueur de data_type. Dans ce cas, elle est tronquée. Pour un data_type de type caractère de taille fixe, si la longueur du résultat converti est inférieure à la longueur de data_type, des espaces sont ajoutés à droite de l’expression convertie pour conserver un nombre pair de chiffres hexadécimaux. Les caractères 0x ne sont pas ajoutés à gauche du résultat converti pour le style 2. |
Conversions implicites
Pour les conversions implicites, il n’est pas nécessaire de spécifier la fonction CAST ou CONVERT. En revanche, les conversions explicites requièrent la spécification de la fonction CAST ou CONVERT. L’illustration ci-dessous reprend toutes les conversions de types de données explicites et implicites autorisées pour les types de données SQL Server fournis par le système. Ces types sont notamment bigint, sql_variant et xml. 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.
Conseil
Ce graphique est disponible en fichier PNG téléchargeable dans le Centre de téléchargement Microsoft.
Le graphique ci-dessus illustre toutes les conversions implicites et explicites autorisées dans SQL Server, mais le type de données résultant de la conversion dépend de l’opération effectuée :
- Pour les conversions explicites, l’instruction elle-même détermine le type de données résultant.
- Pour les conversions implicites, les instructions d’assignation, comme la définition de la valeur d’une variable ou l’insertion d’une valeur dans une colonne, produisent le type de données défini par la déclaration de variable ou la définition de colonne.
- Pour les opérateurs de comparaison ou d’autres expressions, le type de données résultant dépend des règles de priorité des types de données.
Conseil
Un exemple pratique sur les effets de la priorité des types de données dans les conversions peut être consulté plus loin dans cette section.
Quand vous effectuez une conversion entre datetimeoffset et les types de caractères char, nchar, nvarchar et varchar, la partie du décalage de fuseau horaire convertie doit toujours comporter des chiffres doubles pour HH et pour MM. Par exemple : -08:00.
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 ne retourne pas la valeur hexadécimale 41. Elle retourne la valeur hexadécimale 4100 :
SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary);
Pour plus d’informations, consultez Prise en charge d’Unicode et du classement.
Types de données de valeur élevée
Les types de données correspondant à des valeurs élevées ont le même comportement de conversion, à la fois implicite et explicite, que leurs équivalents de valeurs plus faibles, notamment en ce qui concerne les types nvarchar, varbinary et varchar. Vous devez cependant prendre en compte les recommandations suivantes :
- Une conversion du type image en varbinary(max) et vice versa fonctionne comme une conversion implicite et il en va de même pour les conversions entre les types text et varchar(max) , et ntext et nvarchar(max) .
- La conversion de types de données de valeur élevée, comme c’est le cas pour le type varchar(max) , en type équivalent de valeur 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 valeur plus modeste.
- Les conversions de nvarchar, varbinary ou varchar en leurs équivalents de valeur élevée se déroulent de façon implicite.
- Enfin, les conversions du type de données sql_variant en type plus conséquent correspondent cependant à une conversion explicite.
- Les types de données de valeur élevée ne peuvent pas être convertis en sql_variant.
Pour plus d’informations sur la conversion à partir du type de données xml, consultez Créer des instances de données 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 binaire, le contenu du type de données xml est sérialisé par rapport à un ensemble défini de règles. Pour plus d’informations sur ces règles, consultez Définir la sérialisation des données XML. Pour plus d’informations sur la conversion des autres types de données vers le type xml, consultez Créer des instances de données 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 type de données. Vous pouvez convertir explicitement des données de type text en données caractères, et des données de type image en données binary ou varbinary, mais la longueur maximale est de 8 000 octets. Si vous tentez une conversion incorrecte, par exemple une expression de type caractères incluant des lettres et convertie en type int, SQL Server retourne un message d’erreur.
Classement des résultats
Quand 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. Par 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ères (binary, char, nchar, nvarchar, varbinary ou varchar) en une expression d’un type de données différent, l’opération de conversion peut tronquer les données de sortie, afficher uniquement en partie les données de sortie ou retourner une erreur. Ces situations se produisent si le résultat est trop court pour pouvoir être affiché. Les conversions en binary, char, nchar, nvarchar, varbinary ou varchar sont tronquées, sauf pour celles répertoriées dans le tableau suivant.
| Du type de données | Au type de données | Résultats |
|---|---|---|
| int, smallint ou tinyint | char varchar |
Trop court pour être affiché |
| nchar nvarchar |
Erreur 1 | |
| money, smallmoney, numeric, decimal, float ou real | char varchar |
Erreur 1 |
| nchar nvarchar |
Erreur 1 |
1 Erreur retournée car 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, restituent 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));
GO
Avertissement
N’essayez pas de construire des valeurs binary, puis de les convertir en un type de données de catégorie numérique. SQL Server ne garantit pas que le résultat d’une conversion de type de données decimal ou numeric en binary est le 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 AdventureWorks2022;
GO
SELECT p.FirstName,
p.LastName,
SUBSTRING(p.Title, 1, 25) AS Title,
CAST(e.SickLeaveHours AS CHAR(1)) AS [Sick Leave]
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID > 5;
GO
Voici le jeu de résultats obtenu.
FirstName LastName Title Sick Leave
--------- ------------- ------- --------`
Ken Sanchez NULL *
Terri Duffy NULL *
Roberto Tamburello NULL *
Rob Walters NULL *
Gail Erickson Ms. *
(5 row(s) affected)
Quand vous convertissez des types de données qui diffèrent par le nombre de décimales, SQL Server retourne parfois une valeur résultante tronquée et une valeur arrondie à d’autres moments. Le tableau suivant illustre ce comportement.
| À partir | À | Comportement |
|---|---|---|
| numeric | numeric | Round |
| numeric | int | Tronquer |
| numeric | money | Round |
| money | int | Round |
| money | numeric | Round |
| float | int | Tronquer |
| float | numeric | Round 1 |
| float | datetime | Round |
| datetime | int | Round |
1 La conversion des valeurs float qui utilisent la notation scientifique en decimal ou numeric est limitée à des valeurs d’une précision de 17 chiffres uniquement. Toute valeur avec une précision plus élevée que 17 sera arrondie à zéro.
Par exemple, les valeurs 10,6496 et -10,6496 peuvent être tronquées ou arrondies pendant la conversion en types int ou numeric :
SELECT CAST(10.6496 AS INT) AS trunc1,
CAST(-10.6496 AS INT) AS trunc2,
CAST(10.6496 AS NUMERIC) AS round1,
CAST(-10.6496 AS NUMERIC) AS round2;
Les résultats de la requête sont présentés dans le tableau suivant :
| trunc1 | trunc2 | round1 | round2 |
|---|---|---|---|
| 10 | -10 | 11 | -11 |
Lors de la conversion de types de données où le type de données cible compte moins de chiffres décimaux que le type de données source, la valeur obtenue est arrondie. Par exemple, la conversion suivante retourne $10.3497 :
SELECT CAST(10.3496847 AS money);
SQL Server retourne un message d’erreur lors de la conversion de données char, nchar, nvarchar ou varchar non numériques en données decimal, float, int ou numeric. SQL Server retourne également un message d’erreur lorsqu’une chaîne vide (« ») est convertie en données de type numeric ou decimal.
Certaines conversions de date/heure sont non déterministes.
Les styles pour lesquels la conversion chaîne-datetime est non déterministe sont les suivants :
- Tous les styles inférieurs à 100 1
- 106
- 107
- 109
- 113
- 130
1 À l’exception des styles 20 et 21
Pour plus d’informations, consultez Conversion non déterministe de chaînes de date littérale en valeurs DATE.
Caractères supplémentaires (paires de substitution)
À compter de SQL Server 2012 (11.x), si vous utilisez les classements de caractères supplémentaires (SC), une opération CAST du type nchar ou nvarchar en type nchar ou nvarchar de plus petite longueur n’est pas tronquée à l’intérieur d’une paire de substitution. Au lieu de cela, elle est tronquée avant le caractère supplémentaire. Par exemple, le fragment de code suivant laisse @x avec seulement 'ab'. Il n’y a pas assez d’espace pour conserver le caractère supplémentaire.
DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);
SELECT CAST(@x AS NVARCHAR(3));
Lors de l’utilisation de classements SC, le comportement de CONVERT est analogue à celui de CAST. Pour plus d’informations, consultez Prise en charge d’Unicode et du classement, Caractères supplémentaires.
Prise en charge de la compatibilité
Dans les versions antérieures de SQL Server, le style par défaut pour les opérations CAST et CONVERT sur les types de données time et datetime2 est 121, sauf si l’un des types est utilisé dans une expression de colonne calculée. Pour les colonnes calculées, le style par défaut est 0. Ce comportement influe sur les colonnes calculées lorsqu'elles sont créées, utilisées dans des requêtes impliquant le paramétrage automatique, ou utilisées dans des définitions de contraintes.
Quand le niveau de compatibilité a la valeur 110 ou plus, le style par défaut pour les opérations CAST et CONVERT sur les types de données time et datetime2 est toujours 121. Si une requête repose sur l’ancien comportement, utilisez un niveau de compatibilité inférieur à 110, ou spécifiez explicitement le style 0 dans la requête affectée.
| Valeur de niveau de compatibilité | Style par défaut pour CAST et CONVERT 1 |
Style par défaut de la colonne calculée |
|---|---|---|
| <110 | 121 | 0 |
| > = 110 | 121 | 121 |
1 Sauf pour les colonnes calculées
La mise à niveau de la base de données vers le niveau de compatibilité 110 ou plus ne modifie pas les données utilisateur stockées sur le disque. Vous devez corriger manuellement ces données comme il convient. Par exemple, si vous avez utilisé SELECT INTO pour créer une table à partir d’une source contenant une expression de colonne calculée décrite ci-dessus, les données (utilisant le style 0) sont stockées à la place de la définition de colonne calculée. Vous devez mettre à jour manuellement ces données pour qu’elles correspondent au style 121.
Exemples
R. Utiliser simultanément CAST et CONVERT
Ces exemples récupèrent le nom de chaque produit ayant un 3 comme premier chiffre de leur prix, et convertit sa valeur ListPrice en type int.
Utilisez CAST :
USE AdventureWorks2022;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '33%';
GO
Utilisez CONVERT :
USE AdventureWorks2022;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '33%';
GO
Voici le jeu de résultats obtenu. L’exemple de jeu de résultats est le même pour CAST et CONVERT.
ProductName ListPrice
------------------------------ ---------------------
LL Road Frame - Black, 58 337.22
LL Road Frame - Black, 60 337.22
LL Road Frame - Black, 62 337.22
LL Road Frame - Red, 44 337.22
LL Road Frame - Red, 48 337.22
LL Road Frame - Red, 52 337.22
LL Road Frame - Red, 58 337.22
LL Road Frame - Red, 60 337.22
LL Road Frame - Red, 62 337.22
LL Road Frame - Black, 44 337.22
LL Road Frame - Black, 48 337.22
LL Road Frame - Black, 52 337.22
Mountain-100 Black, 38 3374.99
Mountain-100 Black, 42 3374.99
Mountain-100 Black, 44 3374.99
Mountain-100 Black, 48 3374.99
HL Road Front Wheel 330.06
LL Touring Frame - Yellow, 62 333.42
LL Touring Frame - Blue, 50 333.42
LL Touring Frame - Blue, 54 333.42
LL Touring Frame - Blue, 58 333.42
LL Touring Frame - Blue, 62 333.42
LL Touring Frame - Yellow, 44 333.42
LL Touring Frame - Yellow, 50 333.42
LL Touring Frame - Yellow, 54 333.42
LL Touring Frame - Yellow, 58 333.42
LL Touring Frame - Blue, 44 333.42
HL Road Tire 32.60
(28 rows affected)
B. Utiliser CAST avec des opérateurs arithmétiques
Cet exemple 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). Cette valeur est arrondie au nombre entier le plus proche, puis CAST en type de données int.
USE AdventureWorks2022;
GO
SELECT CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS Computed
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO
Voici le jeu de résultats obtenu.
Computed
------
379753754
346698349
257144242
176493899
281101272
0
301872549
212623750
298948202
250784119
239246890
101664220
124511336
97688107
(14 row(s) affected)
C. Utiliser CAST pour la concaténation
Cet exemple illustre la concaténation d’expressions de type non-caractères par le biais de CAST. Il utilise la base de données AdventureWorksDW2022.
SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;
Voici le jeu de résultats obtenu.
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
D. Utiliser CAST pour faciliter la lecture des résultats
Cet exemple s’appuie sur CAST dans la liste SELECT pour convertir la colonne Name en colonne de type char(10). Il utilise la base de données AdventureWorksDW2022.
SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name,
ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
GO
Voici le jeu de résultats obtenu.
Name ListPrice
---------- ---------
Long-Sleev 31.2437
Long-Sleev 32.4935
Long-Sleev 49.99
E. Utiliser CAST avec la clause LIKE
Cet exemple convertit les valeurs SalesYTD de la colonne money en type de données int, puis en type de données char(20) pour que la clause LIKE puisse les utiliser.
USE AdventureWorks2022;
GO
SELECT p.FirstName,
p.LastName,
s.SalesYTD,
s.BusinessEntityID
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS s
ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%';
GO
Voici le jeu de résultats obtenu.
FirstName LastName SalesYTD BusinessEntityID
---------------- ------------------- ---------------- -------------
Tsvi Reiter 2811012.7151 279
Syed Abbas 219088.8836 288
Rachel Valdez 2241204.0424 289
(3 row(s) affected)
F. Utiliser CONVERT ou CAST avec des données au format XML typé
Les exemples suivants illustrent l’utilisation de la fonction CONVERT pour convertir des données en XML typé avec le type et les colonnes de données XML (SQL Server).
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) :
SELECT 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) :
SELECT 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é :
SELECT CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>' AS XML)
Pour obtenir plus d’exemples, consultez Créer des instances de données XML.
G. Utiliser CAST et CONVERT avec des données de type datetime
À compter des valeurs GETDATE(), cet exemple affiche la date et l’heure actuelles, utilise CAST pour convertir la date et l’heure actuelles en type de données caractères, puis utilise CONVERT pour afficher la date et l’heure au format ISO 8601.
SELECT GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
GO
Voici le jeu de résultats obtenu.
UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
----------------------- ---------------------- ------------------------------
2022-04-18 09:58:04.570 Apr 18 2022 9:58AM 2022-04-18T09:58:04.570
(1 row(s) affected)
Cet exemple est plus ou moins l’inverse de l’exemple précédent. Cet exemple affiche une date et une heure sous forme de données caractères, utilise CAST pour convertir les données caractères en type de données datetime, puis utilise CONVERT pour convertir le type de données caractères en type de données datetime.
SELECT '2006-04-25T15:50:59.997' AS UnconvertedText,
CAST('2006-04-25T15:50:59.997' AS DATETIME) AS UsingCast,
CONVERT(DATETIME, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601;
GO
Voici le jeu de résultats obtenu.
UnconvertedText UsingCast UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2006-04-25T15:50:59.997 2006-04-25 15:50:59.997 2006-04-25 15:50:59.997
(1 row(s) affected)
H. Utiliser CONVERT avec des données binaires et caractères
Ces exemples montrent les résultats de la conversion de données binaires et caractères en utilisant des styles différents.
--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(CHAR(8), 0x4E616d65, 0) AS [Style 0, binary to character];
Voici le jeu de résultats obtenu.
Style 0, binary to character
----------------------------
Name
(1 row(s) affected)
Cet exemple montre que Style 1 peut forcer la troncation de résultat. Les caractères 0x dans le jeu de résultats forcent la troncation.
SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];
Voici le jeu de résultats obtenu.
Style 1, binary to character
------------------------------
0x4E616D
(1 row(s) affected)
Cet exemple montre que Style 2 ne tronque pas le résultat, car les caractères 0x ne sont pas inclus dans le résultat.
SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];
Voici le jeu de résultats obtenu.
Style 2, binary to character
------------------------------
4E616D65
(1 row(s) affected)
Convertit la valeur de caractère « Name » en valeur binaire.
SELECT CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary];
Voici le jeu de résultats obtenu.
Style 0, character to binary
----------------------------
0x4E616D6500000000
(1 row(s) affected)
SELECT CONVERT(BINARY(4), '0x4E616D65', 1) AS [Style 1, character to binary];
Voici le jeu de résultats obtenu.
Style 1, character to binary
----------------------------
0x4E616D65
(1 row(s) affected)
SELECT CONVERT(BINARY(4), '4E616D65', 2) AS [Style 2, character to binary];
Voici le jeu de résultats obtenu.
Style 2, character to binary
----------------------------------
0x4E616D65
(1 row(s) affected)
I. Convertir des types de données de date et d’heure
Cet exemple illustre la conversion des types de données date, time et datetime.
DECLARE @d1 DATE,
@t1 TIME,
@dt1 DATETIME;
SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();
-- When converting date to datetime the minutes portion becomes zero.
SELECT @d1 AS [DATE],
CAST(@d1 AS DATETIME) AS [date as datetime];
-- When converting time to datetime the date portion becomes zero
-- which converts to January 1, 1900.
SELECT @t1 AS [TIME],
CAST(@t1 AS DATETIME) AS [time as datetime];
-- When converting datetime to date or time non-applicable portion is dropped.
SELECT @dt1 AS [DATETIME],
CAST(@dt1 AS DATE) AS [datetime as date],
CAST(@dt1 AS TIME) AS [datetime as time];
Vérifiez que les valeurs se trouvent dans une plage compatible lorsque vous envisagez une conversion de date en datetime ou datetime2. La valeur minimale de l’année pour datetime est 1753, tandis que la valeur minimale de l’année est 0001 pour date et datetime2.
DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2
SET @d1 = '1492-08-03'
--This is okay; Minimum YYYY for DATE is 0001
SET @dt2 = CAST(@d1 AS DATETIME2)
--This is okay; Minimum YYYY for DATETIME2 IS 0001
SET @dt1 = CAST(@d1 AS DATETIME)
--This will error with (Msg 242) "The conversion of a date data type to a datetime data type resulted in an out-of-range value."
--Minimum YYYY for DATETIME is 1753
J. Utiliser CONVERT avec des données datetime dans différents formats
À partir des valeurs GETDATE(), cet exemple utilise CONVERT pour afficher tous les styles de date et d’heure dans la section Styles de date et d’heure de cet article.
| Numéro de format | Exemple de requête | Exemple de résultat |
|---|---|---|
| 0 | SELECT CONVERT(NVARCHAR, GETDATE(), 0) |
23 août 2019 1:39PM |
| 1 | SELECT CONVERT(NVARCHAR, GETDATE(), 1) |
08/23/19 |
| 2 | SELECT CONVERT(NVARCHAR, GETDATE(), 2) |
19.08.23 |
| 3 | SELECT CONVERT(NVARCHAR, GETDATE(), 3) |
23/08/19 |
| 4 | SELECT CONVERT(NVARCHAR, GETDATE(), 4) |
23.08.19 |
| 5 | SELECT CONVERT(NVARCHAR, GETDATE(), 5) |
23-08-19 |
| 6 | SELECT CONVERT(NVARCHAR, GETDATE(), 6) |
23 août 19 |
| 7 | SELECT CONVERT(NVARCHAR, GETDATE(), 7) |
23 août, 19 |
| 8 ou 24 ou 108 | SELECT CONVERT(NVARCHAR, GETDATE(), 8) |
13:39:17 |
| 9 ou 109 | SELECT CONVERT(NVARCHAR, GETDATE(), 9) |
23 août 2019 1:39:17:090PM |
| 10 | SELECT CONVERT(NVARCHAR, GETDATE(), 10) |
08-23-19 |
| 11 | SELECT CONVERT(NVARCHAR, GETDATE(), 11) |
19/08/23 |
| 12 | SELECT CONVERT(NVARCHAR, GETDATE(), 12) |
190823 |
| 13 ou 113 | SELECT CONVERT(NVARCHAR, GETDATE(), 13) |
23 août 2019 13:39:17:090 |
| 14 ou 114 | SELECT CONVERT(NVARCHAR, GETDATE(), 14) |
13:39:17:090 |
| 20 ou 120 | SELECT CONVERT(NVARCHAR, GETDATE(), 20) |
2019-08-23 13:39:17 |
| 21 ou 25 ou 121 | SELECT CONVERT(NVARCHAR, GETDATE(), 21) |
2019-08-23 13:39:17.090 |
| 22 | SELECT CONVERT(NVARCHAR, GETDATE(), 22) |
23/08/19 1:39:17 PM |
| 23 | SELECT CONVERT(NVARCHAR, GETDATE(), 23) |
2019-08-23 |
| 101 | SELECT CONVERT(NVARCHAR, GETDATE(), 101) |
08/23/2019 |
| 102 | SELECT CONVERT(NVARCHAR, GETDATE(), 102) |
2019.08.23 |
| 103 | SELECT CONVERT(NVARCHAR, GETDATE(), 103) |
23/08/2019 |
| 104 | SELECT CONVERT(NVARCHAR, GETDATE(), 104) |
23.08.2019 |
| 105 | SELECT CONVERT(NVARCHAR, GETDATE(), 105) |
23-08-2019 |
| 106 | SELECT CONVERT(NVARCHAR, GETDATE(), 106) |
23 août 2019 |
| 107 | SELECT CONVERT(NVARCHAR, GETDATE(), 107) |
23 août 2019 |
| 110 | SELECT CONVERT(NVARCHAR, GETDATE(), 110) |
08-23-2019 |
| 111 | SELECT CONVERT(NVARCHAR, GETDATE(), 111) |
2019/08/23 |
| 112 | SELECT CONVERT(NVARCHAR, GETDATE(), 112) |
20190823 |
| 113 | SELECT CONVERT(NVARCHAR, GETDATE(), 113) |
23 août 2019 13:39:17.090 |
| 120 | SELECT CONVERT(NVARCHAR, GETDATE(), 120) |
2019-08-23 13:39:17 |
| 121 | SELECT CONVERT(NVARCHAR, GETDATE(), 121) |
2019-08-23 13:39:17.090 |
| 126 | SELECT CONVERT(NVARCHAR, GETDATE(), 126) |
2019-08-23T13:39:17.09 |
| 127 | SELECT CONVERT(NVARCHAR, GETDATE(), 127) |
2019-08-23T13:39:17.09 |
| 130 | SELECT CONVERT(NVARCHAR, GETDATE(), 130) |
22 ذو الحجة 1440 1:39:17.090P |
| 131 | SELECT CONVERT(NVARCHAR, GETDATE(), 131) |
22/12/1440 1:39:17.090PM |
K. Effets de la priorité des types de données dans les conversions autorisées
L’exemple suivant définit une variable de type varchar(10), assigne une valeur de type entier à la variable, puis sélectionne une concaténation de la variable avec une chaîne.
DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.' AS Result
Voici le jeu de résultats obtenu.
Result
-----------------------
1 is a string.
La valeur int 1 a été convertie en varchar.
Cet exemple montre une requête similaire, avec une variable int à la place :
DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.' AS Result
Dans ce cas, l’instruction SELECT génère l’erreur suivante :
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ' is not a string.' to data type int.
Afin d’évaluer l’expression @notastring + ' is not a string.', SQL Server doit suivre les règles de priorité des types de données pour effectuer la conversion implicite avant le calcul du résultat de l’expression. Étant donné que le type int a une priorité plus élevée que varchar, SQL Server tente de convertir la chaîne en entier, et échoue car cette chaîne ne peut pas être convertie en entier.
Si nous fournissons une chaîne pouvant être convertie, l’instruction réussit, comme dans l’exemple suivant :
DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'
La chaîne '1' pouvant être convertie en valeur entière 1 dans ce cas, l’instruction SELECT retourne la valeur 2. Quand les types de données fournis sont des entiers, l’opérateur + devient l’opérateur mathématique d’addition, plutôt qu’une concaténation de chaînes.
Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)
L. Utiliser CAST et CONVERT
Cet exemple récupère le nom de chaque produit possédant un 3 au premier chiffre de son prix et convertit sa valeur ListPrice en type int. Il utilise la base de données AdventureWorksDW2022.
SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';
Cet exemple montre la même requête, qui utilise CONVERT au lieu de CAST. Il utilise la base de données AdventureWorksDW2022.
SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CONVERT(INT, ListPrice) LIKE '3%';
M. Utiliser CAST avec des opérateurs arithmétiques
Cet exemple effectue un calcul de valeur de colonne unique en divisant le prix unitaire du produit (UnitPrice) par le pourcentage de remise (UnitPriceDiscountPct). Ce résultat est ensuite arrondi au nombre entier le plus proche, puis converti en type de données int. Cet exemple utilise la base de données AdventureWorksDW2022.
SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,
CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355'
AND UnitPriceDiscountPct > .02;
Voici le jeu de résultats obtenu.
ProductKey UnitPrice UnitPriceDiscountPct DiscountPrice
---------- --------- -------------------- -------------
323 430.6445 0.05 22
213 18.5043 0.05 1
456 37.4950 0.10 4
456 37.4950 0.10 4
216 18.5043 0.05 1
N. Utiliser CAST avec la clause LIKE
Cet exemple convertit la valeur ListPrice de la colonne money en type int, puis en type char(20) pour que la clause LIKE puisse l’utiliser. Cet exemple utilise la base de données AdventureWorksDW2022.
SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';
O. Utiliser CAST et CONVERT avec des données de type datetime
Cet exemple affiche la date et l’heure actuelles, utilise CAST pour les convertir en type de données caractères, puis utilise CONVERT pour les afficher au format ISO 8601. Cet exemple utilise la base de données AdventureWorksDW2022.
SELECT TOP(1)
SYSDATETIME() AS UnconvertedDateTime,
CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast,
CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;
Voici le jeu de résultats obtenu.
UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
--------------------- --------------------------- ---------------------------
07/20/2010 1:44:31 PM 2010-07-20 13:44:31.5879025 2010-07-20T13:44:31.5879025
Cet exemple est plus ou moins l’inverse de l’exemple précédent. Cet exemple affiche une date et une heure sous forme de données caractères, utilise CAST pour convertir les données caractères en type de données datetime, puis utilise CONVERT pour convertir le type de données caractères en type de données datetime. Cet exemple utilise la base de données AdventureWorksDW2022.
SELECT TOP(1)
'2010-07-25T13:50:38.544' AS UnconvertedText,
CAST('2010-07-25T13:50:38.544' AS DATETIME) AS UsingCast,
CONVERT(DATETIME, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom_ISO8601
FROM dbo.DimCustomer;
Voici le jeu de résultats obtenu.
UnconvertedText UsingCast UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2010-07-25T13:50:38.544 07/25/2010 1:50:38 PM 07/25/2010 1:50:38 PM
Voir aussi
- FORMAT (Transact-SQL)
- STR (Transact-SQL)
- SELECT (Transact-SQL)
- Fonctions système (Transact-SQL)
- Prise en charge d'Unicode et du classement