SQL Server String function – FORMAT- Denali

Leave a comment (0) Go to comments

SQL Server Denali CTP3 has introduces a new function FORMAT() for formatting of DATETIME and numeric values .

FORMAT () – Converts a date time type or number type value to text string according to the specified format.

In previous versions this can be accomplished using CAST and CONVERT as below:

-- Alternate of FORMAT Fuction
DECLARE @d DATETIME = GETDATE()
 
SELECT CONVERT(VARCHAR, @d, 103) AS 'dd/MM/yyyy',
       CONVERT(VARCHAR, @d, 104) AS 'dd.MM.yyyy',
       CONVERT(VARCHAR, @d, 106) AS 'dd mon yyyy',
       CONVERT(VARCHAR, @d, 107) AS 'Mon dd, yyyy'

Here is the result of above query

SQL Server String function   FORMAT  Denali SQL Server Format function string


Now in Denali CTP3 onwards the same results can be accomplished using the FORMAT() , e.g

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'

another example to use FORMAT() with numeric type values

SQL Server String function   FORMAT  Denali SQL Server Format function string

more information about FORMAT() on BOL

EOF - SQL Server String function – FORMAT- Denali, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.