SQL Server 2012 Format Function

SQL Server 2012 has introduced many new functions. We will see some of them in this series.

One of the most common challenge for a SQL Developer is to format a date. In earlier versions, we had to use the CONVERT function with format style number. In SQL Server 2012, we have a new function named FORMAT which can format dates in various formats.

The following codes are self explanatory

declare @d datetime
set @d='20110119 12:33:22'

--Format date in dd-mm-yyyy format
select format(@d,'dd-MM-yyyy') as [dd_mm_yyyy]

--Format date in mm/dd/yyyy format
select format(@d,'MM/dd/yyyy') as [mm/dd/yyyy]

--Format date in mmm-yyyy format
select format(@d,'MMM-yyyy') as [MMM-yyyy]

--Format date in MMM dd,yyyy format
select format(@d,'MMM dd,yyyy') as [MMM dd,yyyy]

--Format date in HH:MM:SS format
select format(@d,'HH:mm:ss') as [HH:mm:ss]

--Format date in long format
select format(@d,'dddd, dd MMMM yyyy') as [long format]

All you have to do is to give the format in a specific way you want. Here’s the output