SQL Server 2012 has introduced many new functions.
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=GETDATE()
--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]