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


Most of SQL Server developers/DBAs think that convertingDateTime to a specific format is bit difficult as you need to remember specificformat number, which you can use with CONVERT(). Like if you need to convertdate 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 theseconversion 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”

Advertisements

Posted on August 6, 2011, in SQL Server Denali, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: