En SQL Server, vous pouvez utiliser la fonction T-SQL FORMAT() pour renvoyer des valeurs telles que des nombres et des dates sous forme de chaînes formatées.
Vous fournissez la valeur à formater et vous spécifiez le format à utiliser. La fonction accepte un argument facultatif qui vous permet de spécifier une culture à utiliser lors du formatage de la valeur.
Syntaxe
La syntaxe se présente comme suit :
FORMAT ( value, format )
Où value est la valeur à formater, et format est une chaîne de format valide qui spécifie le format souhaité.
L’argument facultatif culture peut être utilisé pour spécifier une culture. S’il est omis, la langue de la session actuelle est utilisée.
La fonction FORMAT est non déterministe.
Exemple 1 – Formater un nombre
Voici un exemple de formatage d’un nombre :
SELECT FORMAT(1, 'N') AS Result;
Résultat :
+----------+| Result ||----------|| 1.00 |+----------+
Dans ce cas, j’ai utilisé N comme chaîne de format. Il s’agit d’un spécificateur de format numérique standard permettant d’éditer la valeur sous forme de nombre. Ce spécificateur de format particulier entraîne le formatage de la sortie avec des chiffres entiers et décimaux, des séparateurs de groupe et un séparateur décimal avec un signe négatif facultatif. Ce spécificateur de format est insensible à la casse, donc soit N, soit n convient.
Exemple 2 – Formatage en devise
Voici un exemple de formatage d’un nombre en devise :
SELECT FORMAT(1, 'C') AS Result;
Résultat :
+----------+| Result ||----------|| $1.00 |+----------+
Plus de formats numériques
Il existe de nombreuses autres chaînes de format qui peuvent être utilisées avec la fonction FORMAT(). Par exemple, il existe des chaînes de format pour les pourcentages, la virgule fixe, l’exponentiel (scientifique), l’hexadécimal, et plus encore.
Les chaînes de format numériques sont séparées en chaînes standard et personnalisées.
Les deux articles suivants répertorient toutes les chaînes de format numérique, y compris des exemples :
- Chaînes de format numérique standard
- Chaînes de format numérique personnalisées
Voir également Comment formater les nombres dans SQL Server pour plus d’exemples de formatage des nombres.
Exemple 3 – Formater une date
Voici un exemple de formatage d’une date :
SELECT GETDATE() AS 'Unformatted Date', FORMAT( GETDATE(), 'D') AS 'Formatted Date';
Résultat :
+-------------------------+------------------------+| Unformatted Date | Formatted Date ||-------------------------+------------------------|| 2019-05-08 06:16:55.613 | Wednesday, May 8, 2019 |+-------------------------+------------------------+
Dans ce cas, j’ai utilisé D qui spécifie un modèle de date long.
D’autres formats de date
Il existe de nombreuses autres chaînes de format qui peuvent être utilisées pour formater les dates. Comme pour les chaînes de format numérique, les chaînes de format de date et d’heure sont séparées en standard et personnalisé, de sorte que vous pouvez construire vos propres formats personnalisés, ou vous appuyer sur un format standard.
Les deux articles suivants répertorient toutes les chaînes de format de date et d’heure, y compris des exemples :
- Chaînes de format de date et d’heure standard
- Chaînes de format de date et d’heure personnalisées
Voir également Comment formater la date & Heure dans SQL Server pour plus d’exemples.
Exemple 4 – L’argument facultatif de la culture
Voici un exemple d’utilisation de l’argument culture pour renvoyer une valeur dans différentes devises :
SELECT FORMAT(1, 'C', 'fr-FR') AS 'France', FORMAT(1, 'C', 'th-TH') AS 'Thailand', FORMAT(1, 'C', 'ja-JP') AS 'Japan';
Résultat:
+----------+------------+---------+| France | Thailand | Japan ||----------+------------+---------|| 1,00 € | ฿1.00 | ¥1 |+----------+------------+---------+
La fonction FORMAT() accepte toute culture prise en charge par le .NET Framework comme argument ; elle n’est pas limitée aux langues explicitement prises en charge par SQL Server.
Si vous ne fournissez pas cet argument, la langue de la session actuelle est utilisée.
Pour plus d’exemples, voir Comment les paramètres de langue peuvent affecter vos résultats FORMAT().
Exemple 5 – Argument de culture invalide
Si vous fournissez une culture invalide, vous obtiendrez une erreur :
SELECT FORMAT(1, 'C', 'oop-SS!') AS 'Oops!';
Résultat :
The culture parameter 'oop-SS!' provided in the function call is not supported.
Exemple 6 – Valeur de format invalide
Cependant, pour les autres erreurs, la fonction renvoie NULL. Par exemple, voici ce qui se passe si je fournis une valeur invalide à formater :
SELECT FORMAT(GETDATE(), 'C') AS 'Result';
Résultat :
+----------+| Result ||----------|| NULL |+----------+
Dans ce cas, j’essayais de formater une date en une devise, et le résultat était donc NULL.
Types de données valides
FORMAT() repose sur la présence du Common Language Runtime (CLR) de .NET Framework, et utilise les règles de formatage du CLR.
Les types de données suivants peuvent être formatés avec la fonction FORMAT(). Cette liste contient les types de données acceptables pour la chaîne d’entrée ainsi que leurs types équivalents de mappage .NET Framework.
| Catégorie | Type | .NET type |
|---|---|---|
| Numérique | bigint | Int64 | Numérique | int | Int32 | Numérique | smallint | Int16 | Numérique | tinyint | Byte | . | Numérique | décimal | SqlDécimal | Numérique | numérique | SqlDécimal | Numérique | float | Double | Numérique | réel | Simple |
| Numérique | mallmoney | Décimal |
| Numérique | money | Décimal |
| Date et heure | date | DateHeure | Date et heure | heure | TimeSpan |
| Date et Heure | datetime | DateHeure | Date et Heure | smalldatetime | DateHeure | Date et Heure | datetime2 | DateTime | Date et Heure | datetimeoffset | DateTimeOffset |
Escaping Colons and Periods for the ‘time’ Data Type
Lorsque vous utilisez FORMAT, les deux-points et les points doivent être échappés (ceci adhère aux règles de formatage du CLR). Par conséquent, lorsque la chaîne de format (deuxième paramètre) contient un deux-points ou un point, le deux-points ou le point doit être échappé avec une barre oblique inverse lorsqu’une valeur d’entrée (premier paramètre) est du type de données temps.
Exemple:
SELECT CAST('12:15' AS time) AS 'Unformatted Data', FORMAT(CAST('12:15' AS time), N'hh.mm') AS 'Unescaped', FORMAT(CAST('12:15' AS time), N'hh\.mm') AS 'Escaped';
Résultat:
+--------------------+-------------+-----------+| Unformatted Data | Unescaped | Escaped ||--------------------+-------------+-----------|| 12:15:00 | NULL | 12.15 |+--------------------+-------------+-----------+
Donc, comme prévu, la chaîne non échappée renvoie NULL.
Comme mentionné, cela ne s’applique qu’au type de données temps. Si nous changeons la valeur d’entrée pour un type de données différent, nous n’avons pas besoin de l’échapper :
SELECT CAST('12:15' AS datetime) AS 'Unformatted Data', FORMAT(CAST('12:15' AS datetime), N'hh.mm') AS 'Unescaped', FORMAT(CAST('12:15' AS datetime), N'hh.mm') AS 'Escaped';
Résultat:
+-------------------------+-------------+-----------+| Unformatted Data | Unescaped | Escaped ||-------------------------+-------------+-----------|| 1900-01-01 12:15:00.000 | 12.15 | 12.15 |+-------------------------+-------------+-----------+
Dans ce cas, la valeur d’entrée est datetime, et donc, le résultat est bien sans l’échapper.
Vous pouvez également utiliser la barre oblique inversée pour échapper à tout autre caractère que vous voulez inclure dans la chaîne de résultats, qui serait autrement interprété comme un spécificateur de format. Faire précéder un caractère d’une barre oblique inverse signifie que le caractère suivant est un littéral de caractère qui doit être inclus dans la chaîne de résultats sans modification.
Dans une chaîne de format de date et d’heure personnalisée, les dfFghHKmMstyz:, ou / sont interprétés comme des spécificateurs de format personnalisés plutôt que comme des caractères littéraux.
Dans une chaîne de format numérique personnalisée, les #0.,%, et ‰ sont interprétés comme des spécificateurs de format plutôt que comme des caractères littéraux. Les majuscules et minuscules E ainsi que les symboles + et - peuvent également être interprétés comme des spécificateurs de format, selon leur position dans la chaîne de format.
Si vous devez inclure une barre oblique inverse dans la chaîne de résultats, échappez-la avec une autre barre oblique inverse.
Remoting
La fonction FORMAT() ne peut pas être remotée car elle dépend de la présence du CLR. Remotter une fonction qui nécessite le CLR, pourrait provoquer une erreur sur le serveur distant.
Quand utiliser la fonction FORMAT()
Microsoft recommande d’utiliser la fonction FORMAT() pour le formatage tenant compte des spécificités locales des valeurs de date/heure et de nombre sous forme de chaînes de caractères, et que, pour les conversions générales de types de données, on utilise plutôt la fonction CAST() ou la fonction CONVERT().