Database Simplified Headline Animator

Database Simplified

Saturday 24 September 2011

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

image

 

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

image

 

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

image

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

image

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

image

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

image

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.

No comments:

Post a Comment