Home » , » Format Function in Sql Server 2012

Format Function in Sql Server 2012

Penulis : Sandeep Mittal on Sunday, January 29, 2012 | 1/29/2012 11:45:00 PM

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
Share this article :

Post a Comment