Sunday, January 29, 2012

Format Function in Sql Server 2012

Finally, Microsoft introduced Format function in SQL Server 2012, code name denali that is very handy to format dates, times and currency in different cultures and custom format

In this article, I am sharing some of the examples of how to format date, time, currency and number with format function.

  • Format Date with culture
DECLARE @DATE DATE = GETDATE()
SELECT   FORMAT(@DATE, 'd', 'en-US') AS [Date (US)]
       , FORMAT(@DATE, 'd', 'en-IN') AS [Date (India)]
       , FORMAT(@@DATE, 'd', 'en-GB') AS [Date (Great Britain)]
       , FORMAT(@DATE, 'd', 'de-DE') AS [Date (Denmark)] 
OUTPUT


  • Format Date with custom format
DECLARE @DATE DATETIME = GETDATE()
SELECT   FORMAT(@DATE, 'dd/MM/yyyy') AS [Date (DD/MM/YYYY)]
        , FORMAT(@DATE, 'MM/dd/yyyy') AS [Date (MM/DD/YYYY)]
        , FORMAT(@DATE, 'MM/dd/yyyy ') AS [Date (MM/DD/YYYY)]
        , FORMAT(@DATE, 'MM/dd/yyyy hh:mm:ss tt') AS [Date (MM/DD/YYYY HH:MM:SS)]
        , FORMAT(@DATE, 'MM/dd/yyyy HH:mm:ss') AS [Date (MM/DD/YYYY H24:MM:SS)]
        , FORMAT(@DATE,'ddd') AS [Week Day]
        , FORMAT(@DATE,'MMM') AS [Short Month Name]
        , FORMAT(@DATE,'MMMM') AS [Full Month Name]
        , FORMAT(@DATE,'yyyy') AS [Year
OUTPUT

  • Format Date with short codes
DECLARE @DATE DATETIME = GETDATE()
SELECT    FORMAT(@DATE,'d') AS [Short date pattern]
        , FORMAT(@DATE,'D') AS [Long Date pattern]        
        , FORMAT(@DATE,'t') AS [Short Time pattern]
        , FORMAT(@DATE,'T') AS [Long Time pattern]
OUTPUT

  • Format Currency with culture
DECLARE @Amount MONEY = 210525.52;    
SELECT    FORMAT(@Amount,'c','en-US') [Money (US)]
        , FORMAT(@Amount,'c','en-IN') [Money (India)]
        , FORMAT(@Amount,'c','en-GB') [Money (Great Britain)]
        , FORMAT(@Amount,'c','fr') [Money (France)]
        , FORMAT(@Amount,'c','de-DE') [Money (Denmark)]
        , FORMAT(@Amount,'c','ru-RU') [Money (Russia)]
OUTPUT
  • Format Percentage
DECLARE @Per DECIMAL(4,4) = 0.5545;
SELECT    FORMAT(@Per,'p0') [Percentage (Without decimal)]
        , FORMAT(@Per,'p1') [Percentage (With 1 decimal)]
        , FORMAT(@Per,'p2') [Percentage (With 2 decimal)]
OUTPUT
  • Format Number
DECLARE @num FLOAT=1234567.89
SELECT    FORMAT(@num,'N') AS 'Format Number Defaul'
        , FORMAT(@num,'#,#.00') AS 'Format Number with 2 decimal'
        , FORMAT(@num,'#,#.0') AS 'Format Number with 1 decimal'
OUTPUT

0 comments:

Post a Comment