In SQL Server kunt u de T-SQL FORMAT() functie gebruiken om waarden zoals getallen en data als geformatteerde strings te retourneren.
U geeft de waarde op die geformatteerd moet worden, en u specificeert de te gebruiken opmaak. De functie accepteert een optioneel argument waarmee u een cultuur kunt opgeven die moet worden gebruikt bij het formatteren van de waarde.
Syntax
De syntax gaat als volgt:
FORMAT ( value, format )
Waarbij value de waarde is die u geformatteerd wilt hebben, en format een geldige format string is die het gewenste format specificeert.
Het optionele culture argument kan worden gebruikt om een cultuur op te geven. Indien weggelaten, wordt de taal van de huidige sessie gebruikt.
De FORMAT functie is nondeterministisch.
Voorbeeld 1 – Een getal opmaken
Hier volgt een voorbeeld van het opmaken van een getal:
SELECT FORMAT(1, 'N') AS Result;
Resultaat:
+----------+| Result ||----------|| 1.00 |+----------+
In dit geval heb ik N gebruikt als de format string. Dit is een standaard numerieke format specifier voor het uitvoeren van de waarde als een getal. Deze specifieke format specifier resulteert in de uitvoer die wordt geformatteerd met integraal en decimaal cijfers, groep scheidingstekens, en een decimaal scheidingsteken met optioneel negatief teken. Deze format specifier is hoofdletter-ongevoelig, dus N of n is prima.
Voorbeeld 2 – Formatteren naar een valuta
Hier volgt een voorbeeld van het formatteren van een getal als een valuta:
SELECT FORMAT(1, 'C') AS Result;
Resultaat:
+----------+| Result ||----------|| $1.00 |+----------+
Meer Numerieke Formaten
Er zijn nog veel meer format strings die gebruikt kunnen worden met de FORMAT() functie. Er zijn bijvoorbeeld format strings voor percentages, fixed-point, exponentieel (wetenschappelijk), hexadecimaal, en meer.
De numerieke format strings zijn onderverdeeld in standaard en aangepaste strings.
De volgende twee artikelen geven een overzicht van alle numerieke opmaakstrings, inclusief voorbeelden:
- Standaard numerieke opmaakstrings
- Aangepaste numerieke opmaakstrings
Zie ook Hoe getallen op te maken in SQL Server voor meer voorbeelden van het opmaken van getallen.
Voorbeeld 3 – Een datum opmaken
Hier volgt een voorbeeld van het opmaken van een datum:
SELECT GETDATE() AS 'Unformatted Date', FORMAT( GETDATE(), 'D') AS 'Formatted Date';
Resultaat:
+-------------------------+------------------------+| Unformatted Date | Formatted Date ||-------------------------+------------------------|| 2019-05-08 06:16:55.613 | Wednesday, May 8, 2019 |+-------------------------+------------------------+
In dit geval heb ik D gebruikt, waarmee een lang datumpatroon wordt gespecificeerd.
Meer datumnotaties
Er zijn veel meer notatie-strings die kunnen worden gebruikt om datums op te maken. Net als bij de numerieke opmaakreeksen zijn datum- en tijdopmaakreeksen gescheiden in standaard en aangepaste opmaakreeksen, zodat u uw eigen aangepaste opmaak kunt maken, of u kunt vertrouwen op een standaard opmaak.
De volgende twee artikelen geven een overzicht van alle datum en tijd opmaak strings, inclusief voorbeelden:
- Standaard datum en tijd opmaak strings
- Aangepaste datum en tijd opmaak strings
Zie ook Hoe de Datum te formatteren & Tijd in SQL Server voor meer voorbeelden.
Voorbeeld 4 – Het optionele cultuurargument
Hier volgt een voorbeeld van het gebruik van het culture argument om een waarde in verschillende valuta’s terug te geven:
SELECT FORMAT(1, 'C', 'fr-FR') AS 'France', FORMAT(1, 'C', 'th-TH') AS 'Thailand', FORMAT(1, 'C', 'ja-JP') AS 'Japan';
Resultaat:
+----------+------------+---------+| France | Thailand | Japan ||----------+------------+---------|| 1,00 € | ฿1.00 | ¥1 |+----------+------------+---------+
De FORMAT() functie accepteert elke cultuur die door het .NET Framework als argument; het is niet beperkt tot de talen die expliciet door SQL Server worden ondersteund.
Als u dit argument niet opgeeft, wordt de taal van de huidige sessie gebruikt.
Voor meer voorbeelden, zie Hoe taalinstellingen van invloed kunnen zijn op uw FORMAT() resultaten.
Voorbeeld 5 – Ongeldig cultuurargument
Als u een ongeldige cultuur opgeeft, krijgt u een foutmelding:
SELECT FORMAT(1, 'C', 'oop-SS!') AS 'Oops!';
Resultaat:
The culture parameter 'oop-SS!' provided in the function call is not supported.
Voorbeeld 6 – Ongeldige Formaatwaarde
Bij andere fouten geeft de functie echter NULL terug. Dit is bijvoorbeeld wat er gebeurt als ik een ongeldige waarde opgeef om te formatteren:
SELECT FORMAT(GETDATE(), 'C') AS 'Result';
Resultaat:
+----------+| Result ||----------|| NULL |+----------+
In dit geval probeerde ik een datum te formatteren in een valuta, en daarom was het resultaat NULL.
Valabele gegevenstypen
FORMAT() vertrouwt op de aanwezigheid van het .NET Framework Common Language Runtime (CLR), en gebruikt de CLR-opmaakregels.
De volgende gegevenstypen kunnen worden opgemaakt met de functie FORMAT(). Deze lijst bevat de aanvaardbare gegevenstypen voor de invoertekenreeks samen met hun .NET Framework mapping equivalente typen.
| Categorie | Type | .NET type |
|---|---|---|
| Numeriek | bigint | Int64 |
| Numeriek | int | Int32 |
| Numeric | smallint | Int16 |
| Numeric | tinyint | Byte |
| Numeriek | decimaal | SqlDecimaal |
| Numeriek | numeriek | SqlDecimaal |
| Numeriek | float | Double |
| Numeriek | real | Single |
| Numeriek | smallmoney | Decimal |
| Numeriek | geld | Decimaal |
| datum | DatumTijd | |
| tijd | TijdSpan | |
| datetime | DateTime | |
| Datum en Tijd | smalldatetime | DateTime |
| Datum en Tijd | datetime2 | DatumTijd |
| Datum en Tijd | datetimeoffset | DatumTijdOffset |
Het escapen van dubbele punten en punten voor het gegevenstype ‘tijd’
Wanneer FORMAT gebruikt wordt, moeten dubbele punten en punten worden ge-escaped (dit is in overeenstemming met de CLR opmaak regels). Daarom, wanneer de format string (tweede parameter) een dubbele punt of punt bevat, moet de dubbele punt of punt worden ge-escaped met een backslash wanneer een input waarde (eerste parameter) van het datatype tijd is.
Voorbeeld:
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';
Resultaat:
+--------------------+-------------+-----------+| Unformatted Data | Unescaped | Escaped ||--------------------+-------------+-----------|| 12:15:00 | NULL | 12.15 |+--------------------+-------------+-----------+
Dus zoals verwacht levert de niet-geëscaped string NULL.
Zoals gezegd, geldt dit alleen voor het gegevenstype tijd. Als we de invoerwaarde veranderen in een ander gegevenstype, hoeven we niet te escapen:
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';
Resultaat:
+-------------------------+-------------+-----------+| Unformatted Data | Unescaped | Escaped ||-------------------------+-------------+-----------|| 1900-01-01 12:15:00.000 | 12.15 | 12.15 |+-------------------------+-------------+-----------+
In dit geval is de invoerwaarde datetime, en daarom is het resultaat prima zonder te escapen.
U kunt de backslash ook gebruiken om elk ander teken te escapen dat u in de resultaatstring wilt opnemen en dat anders zou worden geïnterpreteerd als een format specifier. Een teken voorafgegaan door een backslash betekent dat het volgende teken een letterteken is dat ongewijzigd in de resultaatstring moet worden opgenomen.
In een aangepaste opmaakstring voor datum en tijd zijn de dfFghHKmMstyz:, of / karakters worden geïnterpreteerd als aangepaste formaatspecificaties in plaats van als letterlijke karakters.
In een aangepaste numerieke format string, de #0.,%, en ‰ symbolen worden geïnterpreteerd als formaataanduidingen in plaats van als letterlijke tekens. De hoofdletters en kleine letters E, evenals de + en - symbolen kunnen ook worden geïnterpreteerd als opmaakspecificaties, afhankelijk van hun positie binnen de opmaakstring.
Als u een backslash in de resultaatstring moet opnemen, escape deze dan met een andere backslash.
Remoting
De FORMAT() functie kan niet worden geremote omdat deze afhankelijk is van de aanwezigheid van de CLR. Het remoten van een functie die de CLR vereist, kan een fout op de remote server veroorzaken.
Wanneer de functie FORMAT() gebruiken
Microsoft raadt aan de functie FORMAT() te gebruiken voor lokale opmaak van datum/tijd en getalwaarden als tekenreeksen, en dat voor algemene datatypeconversies ofwel de functie CAST() ofwel de functie CONVERT() moet worden gebruikt.