Date Time Formatting With Format() Function In SQL Server 2012 (“Code Named Denali”)

 

Format:  Is the New Function Introduced with SQL Server Denali. It Converts Values into specified format. This function provides more flexibility and ease of getting different format. Here we will see how you can desired format of date and time.

Lets Explore FORMAT() for Date And Time.

Day Format (d)

Select 1 as Id,'d' as FormatCode,FORMAT(GETDATE(),'d') as Format
Union All
Select 2 as Id,'dd' as FormatCode,FORMAT(GETDATE(),'dd') as Format
Union All
Select 3 as Id,'ddd' as FormatCode,FORMAT(GETDATE(),'ddd') as Format
Union All
Select 4 as Id,'dddd' as FormatCode,FORMAT(GETDATE(),'dddd') as Format
Order By Id

 

Month Format(m)

Select 1 as Id,'M' as FormatCode,FORMAT(GETDATE(),'M') as Format
Union All
Select 2 as Id,'MM' as FormatCode,FORMAT(GETDATE(),'MM') as Format
Union All
Select 3 as Id,'MMM' as FormatCode,FORMAT(GETDATE(),'MMM') as Format
Union All
Select 4 as Id,'MMMM' as FormatCode,FORMAT(GETDATE(),'MMMM') as Format
Order By Id

 

Year Format (y)

Select 1 as Id,'y' as FormatCode,FORMAT(GETDATE(),'y') as Format
Union All
Select 2 as Id,'yy' as FormatCode,FORMAT(GETDATE(),'yy') as Format
Union All
Select 3 as Id,'yyy' as FormatCode,FORMAT(GETDATE(),'yyy') as Format
Union All
Select 4 as Id,'yyyy' as FormatCode,FORMAT(GETDATE(),'yyyy') as Format
Order By Id

Now With Above Different Format Combination You Can Achieve Different Date Format, Some Examples Are Given Below

Select 0 Id,'dd-MM-yyy' as FormatCode , FORMAT(GETDATE(),'dd-MM-yyy') As Format
Union All
Select 1 Id,'dd MMM' as FormatCode , FORMAT(GETDATE(),'dd MMM') As Format
Union All
Select 2 Id,'dd.MM.yy' as FormatCode,FORMAT(GETDATE(),'dd.MM.yy') As Format
Union All
Select 3 Id,'dd/MM/yyy' as FormatCode, FORMAT(GETDATE(),'dd/MM/yyy') As Format
Union All
Select 4 Id,'dd MMM yy (ddd)' as FormatCode,FORMAT(GETDATE(),'dd MMM yy (ddd)') As Format
Union All
Select 5 Id,'dd MMMM yyyy (dddd)' as FormatCode,FORMAT(GETDATE(),'dd MMMM yyyy (dddd)') As Format
Union All
Select 6 Id,'MMMM yyyy' as FormatCode,FORMAT(GETDATE(),'MMMM yyyy')
Order By Id

Time Part(Hour, Minute, Second and MiliSecond)

Select 1 Id,GETDATE() DateValue,'Hour' FormatCode,FORMAT(GETDATE(),'hh') as Format
Union All
Select 1 Id,GETDATE() DateValue,'Minute' FormatCode,FORMAT(GETDATE(),'mm') as Format
Union All
Select 1 Id,GETDATE() DateValue,'Second' FormatCode,FORMAT(GETDATE(),'ss') as Format
Union All
Select 5 Id,GETDATE() DateValue,'MiliSecond' FormatCode,FORMAT(GETDATE(),'fff') As Format

Examples of Time Formats

Select 1 as Id,'hh:mm:ss tt' as FormatCode,FORMAT(GETDATE(),'hh:mm:ss tt') As Format
Union
Select 2 as Id,'hh:mm tt' as FormatCode,FORMAT(GETDATE(),'hh:mm tt') As Format
Union 
Select 3 as Id,'hh:mm' as FormatCode,FORMAT(GETDATE(),'hh:mm') As Format
Union
Select 4 as Id,'hh ''Hours'' mm ''Mintues'' ss ''Seconds''' as FormatCode,
FORMAT(GETDATE(),'hh ''Hours'' mm ''Mintues'' ss ''Seconds''') As Format
Order By Id

I Am Really happy with the introduction of FORMAT() function in SQL Server Denali as It saves lot of effort in writing sql to get desired format.