For a developer working on MS SQL server the correct formatting of data has always been a challenge. In India when working with dates we follow the dd/mm/yy format as oppsed to the mm/dd/yy format followed in the US. Till now we used to rely on the convert syntax to change the date into a text that had the correct format using a number of different style options.
With the new format syntax we can represent any number and date into a desired format that we need. The most common examples I see of this usage is representing a number in XXXX-XX-XXXX format for forms such as invoices and receipts systems. The below examples show how dates, numbers and currencies can be represented in the new formats using this command. The best thing about this feature is that it allows a level formatting that we are used to seeing in Analysis services more importantly since this formatting is locale aware we can actually make our code dynamic in order to represent the data in ways that are understood by the end user. The only thing to keep in mind is when using currency formats to take into account the exchange rates. While it might seem a minor improvement it saves a lot of effort in SSRS as well as Power Pivot representations of the data.
DECLARE @date DATETIME = getdate()
SELECT format(@date, 'd', 'en-in') --22-05-2014
SELECT format(@date, 'd', 'en-us') --5/22/2014
SELECT format(@date, 'D', 'en-us') --Thursday, May 22, 2014
SELECT format(@date, 'dd/mm/yyyy hh;mm:ss', 'en-us') --22/01/2014 03;01:28
SELECT format(1112231111, '##-##-###') --11122-31-111 notice the truncation
SELECT format(1112231111, '##-##-#######') --1-11-2231111
SELECT FORMAT(1212, 'C', 'en-us')
--$1,212.00 two decimal palces by default
SELECT FORMAT(1212, 'E', 'en-us')
--1.212000E+003 two decimal palces by default
I have always maintained that SQL is not the place to do formatting of data but this is a welcome addition since it simplifies a lot of effort further downstream.
Reference http://msdn.microsoft.com/library/dwhawy9k.aspx
Please Consider Subscribing
