SQL Server Denali: Format(), a Most Wanted Function

Most of SQL Server developers/DBAs think that converting DateTime to a specific format is bit difficult as you need to remember specific format number, which you can use with CONVERT(). Like if you need to convert date to German format i.e. dd.mm.yy, then you can do it as follow:

SELECT CONVERT(NVARCHAR(30),GETDATE(),104) AS GermanDateFormat

Finding it tough to remember these conversion code/number, I put these format codes in my early post. But now using SQL Server Denali, you can use a most demanded function FORMAT().

FORMAT ( value, format [, culture ] )

 

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',

        FORMAT ( @d, 'MMMM dd, yyyy (dddd)') AS 'MMMM dd, yyyy (dddd)',

        FORMAT ( @d, 'dddd MMMM dd, yyyy ') AS 'MMMM dd, yyyy (dddd)',

        FORMAT ( @d, 'hh:mm:ss') AS 'hh:mm:ss'

FORMAT() is not limited to Date/Time only

DECLARE @I int = 15;

SELECT FORMAT(@I,'c') AS Currency,

              FORMAT(@I,'e') AS Scientific,

              FORMAT(@I,'p') AS Percent_,

              FORMAT(@I,'x') AS HexaDecimal

Note: In above examples we have not used, third parameter i.e. culture. If value for culture is not provided then it will use session default language. In my case it is "en-US"

For more information http://msdn.microsoft.com/en-us/library/hh213505%28v=SQL.110%29.aspx


------------------------------------------------------------------------------------
Read More about SQL Server 2012 (Code Name: Denali) 

·         Introducing New Edition "Business Intelligence"

·         Changing Backup Files Default Path is More Easy Now

·         New Backup/Restore Options

·         CTP 3 Product Guide Released

·         TRY_CONVERT(), a Good Addition

·         Table Partition Limit Enhancement

·         Get Date/Time from Parts

·         New Function to Get Last Date of Month

·         IIF Logical Function

·         A New More Flexible Create Index Dialog box