SQL Server Denali: Format()

Converting DateTime to a specific format is bit difficult task as you need to remember specific format number, which you can use with CONVERT(). Like if you need to convert date to dd.mm.yy format, then you can do it as follow:

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

Now using SQL Server Denali, we can use a function FORMAT() to format datetime.

FORMAT ( value, format [, culture ] )

DECLARE @dtDate datetime = GETDATE();

SELECT  FORMAT(@dtDate, 'dd.MM.yyy')  AS 'dd.MM.yy',

        FORMAT(@dtDate, 'dd/MMM/yyy') AS 'dd/MMM/yy',

        FORMAT(@dtDate, 'dd/MM/yyyy') AS 'dd/MM/yyyy',

        FORMAT(@dtDate, 'MMM dd, yy') AS 'MMM dd, yy',

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

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

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

FORMAT() is not formatting only Date/TIME, It format other DataTypes also.

DECLARE @ITemp int = 15;

SELECT FORMAT(@ITemp,'e') AS Scientific,

              FORMAT(@ITemp,'p') AS [Percent],

              FORMAT(@ITemp,'c') AS Currency,

              FORMAT(@ITemp,'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"