FORMAT is a function that is used return a NVARCHAR data type from a date/time or number. You can still use CAST or CONVERT for these and all other conversions. FORMAT is just another option that was introduced in SQL Server 2012, however, FORMAT works with the .NET Framework CLR. If FORMAT encounters an error, it will return a NULL value.
FORMAT using Cultures:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @SampleDate DATETIME; SET @SampleDate = GETDATE(); SELECT FORMAT(@SampleDate, 'd', 'en-us') as 'US English' ,FORMAT(@SampleDate, 'd', 'en-gb') as 'Proper English' ,FORMAT(@SampleDate, 'd', 'de-de') as 'German' ,FORMAT(@SampleDate, 'd', 'zh-cn') as 'Simplified Chinese'; SELECT FORMAT(@SampleDate, 'D', 'en-us') as 'US English' ,FORMAT(@SampleDate, 'D', 'en-gb') as 'Proper English' ,FORMAT(@SampleDate, 'D', 'de-de') as 'German' ,FORMAT(@SampleDate, 'D', 'zh-cn') as 'Simplified Chinese'; GO |
Results:
|
1 2 3 4 5 6 7 8 9 10 11 |
US English Proper English German Simplified Chinese ------------ ---------------- ------------ -------------------- 4/6/2015 06/04/2015 06.04.2015 2015/4/6 (1 row(s) affected) US English Proper English German Simplified Chinese ----------------------- ---------------- ----------------------- -------------------- Monday, April 6, 2015 06 April 2015 Montag, 6. April 2015 2015年4月6日 (1 row(s) affected) |
FORMAT can use any of the many different cultures that are available, you can view all of the different cultures at SQL Server Helper.
FORMAT custom strings:
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @SampleDate DATETIME; DECLARE @SampleNumber INT; SET @SampleDate = GETDATE(); SET @SampleNumber = 4023555599991234 SELECT FORMAT(@SampleDate, 'dd/MM/yyyy') as 'Proper English' ,FORMAT(@SampleNumber, '##### #### #### ####') as 'Credit Card Number'; GO |
Results:
|
1 2 3 4 5 |
Proper English Credit Card Number ---------------- --------------------- 06/04/2015 4023 5555 9999 1234 (1 row(s) affected) |
FORMAT with numberic types:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE AdventureWorks2012; GO SELECT TOP(4) EndOfDayRate ,FORMAT(EndOfDayRate,'C','en-gb') as 'English Pounds' ,FORMAT(EndOfDayRate,'G','en-gb') as 'General' ,FORMAT(EndOfDayRate,'E','en-gb') as 'Exponential' ,FORMAT(EndOfDayRate,'F','en-gb') as 'Fixed Point' ,FORMAT(EndOfDayRate,'P','en-gb') as 'Percent' FROM Sales.CurrencyRate WHERE CurrencyRateDate = '07/01/2005' ORDER BY EndOfDayRate; GO |
Results:
|
1 2 3 4 5 6 7 8 |
EndOfDayRate English Pounds General Exponential Fixed Point Percent -------------- ---------------- --------- ------------- ------------- --------- 0.6183 £0.62 0.6183 6.183000E-001 0.62 61.83% 0.9703 £0.97 0.9703 9.703000E-001 0.97 97.03% 1.00 £1.00 1.0000 1.000000E+000 1.00 100.00% 1.0002 £1.00 1.0002 1.000200E+000 1.00 100.02% (4 row(s) affected) |
More numeric types can be viewed at MSDN Standard Numeric Format Strings.
Leave a Comment