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.
No comments:
Post a Comment