Thursday, August 18, 2011

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 format, then you can do it as follow:
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"

No comments:

Post a Comment