SQL Server – "Denali" – String Formatting using FORMAT()
SQL Server "Denali" introduces a new function FORMAT() for formatting of DATETIME and numeric values. In Earlier versions we did date and time conversions using CAST and CONVERT as below:
– © 2011 – Vishal (http://SqlAndMe.com)
DECLARE @d DATETIME = GETDATE()
SELECT CONVERT(VARCHAR, @d, 103) AS 'dd/MM/yyyy',
CONVERT(VARCHAR, @d, 104) AS 'dd.MM.yyyy',
CONVERT(VARCHAR, @d, 106) AS 'dd mon yyyy',
CONVERT(VARCHAR, @d, 107) AS 'Mon dd, yyyy'
Result Set:
dd/MM/yyyy dd.MM.yyyy dd mon yyyy Mon dd, yyyy
———- ———- ———— ————
20/07/2011 20.07.2011 20 Jul 2011 Jul 20, 2011
(1 row(s) affected)
FORMAT() uses .NET Framework format string. It can be used as below:
DECLARE @d DATETIME = GETDATE()
SELECT FORMAT ( @d, 'dd/MM/yyyy') AS 'dd/MM/yyyy',
FORMAT ( @d, 'dd.MM.yyy') AS 'dd.MM.yy',
FORMAT ( @d, 'dd/MMM/yyy') AS 'dd/MMM/yy',
FORMAT ( @d, 'MMM dd, yy') AS 'MMM dd, yy'
Result Set:
dd/MM/yyyy dd.MM.yy dd/MMM/yy MMM dd, yy
———– ——— ———– ————-
20/07/2011 20.07.2011 20/Jul/2011 Jul 20, 11
(1 row(s) affected)
FORMAT() also supports switching locale.
DECLARE @d DATETIME = GETDATE()
SELECT FORMAT ( @d, 'dd MMMM, yyyy', 'en-US') AS 'dd MMMM, yyyy',
FORMAT ( @d, 'dd MMMM, yyyy', 'fr-FR') AS 'dd MMMM, yyyy',
FORMAT ( @d, 'dd MMMM, yyyy', 'de-DE') AS 'dd MMMM, yyyy'
Result Set:
dd MMMM, yyyy dd MMMM, yyyy dd MMMM, yyyy
————- ————- ————-
20 July, 2011 20 juillet, 2011 20 Juli, 2011
(1 row(s) affected)
You can find more information about FORMAT() on BOL : http://msdn.microsoft.com/en-us/library/hh213505(v=SQL.110).aspx
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
