Archive

Posts Tagged ‘sql date dd/mm/yyyy’

SQL Server – "Denali" – String Formatting using FORMAT()

20.07.2011 No comments

SQL Server "Denali" introduces a new function FORMAT() for formatting of DATETIME and numeric values. In Earlier versions we did date and time conversions using CAST and CONVERT as below:

— © 2011 – Vishal (http://SqlAndMe.com)

 

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'

Result Set:

dd/MM/yyyy    dd.MM.yyyy    dd mon yyyy   Mon dd, yyyy

———-    ———-    ————  ————

20/07/2011    20.07.2011    20 Jul 2011   Jul 20, 2011

 

(1 row(s) affected)

FORMAT() uses .NET Framework format string. It can be used as below:

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'

 

Result Set:

dd/MM/yyyy    dd.MM.yy      dd/MMM/yy     MMM dd, yy

———–   ———     ———–   ————-

20/07/2011    20.07.2011    20/Jul/2011   Jul 20, 11

 

(1 row(s) affected)

FORMAT() also supports switching locale.

DECLARE @d DATETIME = GETDATE()

 

SELECT FORMAT ( @d, 'dd MMMM, yyyy', 'en-US') AS 'dd MMMM, yyyy',

       FORMAT ( @d, 'dd MMMM, yyyy', 'fr-FR') AS 'dd MMMM, yyyy',

       FORMAT ( @d, 'dd MMMM, yyyy', 'de-DE') AS 'dd MMMM, yyyy'

Result Set:

dd MMMM, yyyy        dd MMMM, yyyy        dd MMMM, yyyy

————-        ————-        ————-

20 July, 2011        20 juillet, 2011     20 Juli, 2011

 

(1 row(s) affected)

You can find more information about FORMAT() on BOL : http://msdn.microsoft.com/en-us/library/hh213505(v=SQL.110).aspx

Hope This Helps!

Vishal