format function sql server

format function SQL Server
How to convert date into specific format is most frequently asked question in SQL Server. We used the convert function to do this in SQL Server 2008 and older version of SQL Server. But in 2012 a new function introduced that is format function  & its very useful to format the date and integer value.I am not going to discuss about the performance of format function. I just explore here how to use this new sql server inbuilt function. Format() function return nvarchar which is in desired formatted form. The function format accept three parameter
1. First parameter is date or int or money type which you have to format
2. Second parameter in Format() function is format of string structure. One point you have to must         remember this parameter is case sensitive. Means if you write down mm then its differ from MM.
3. Third and last parameter is optional   and its culture which was supported in .net framework. For    example hi-In in Hindi India culture.
So syntax of this SQL server function is
FORMAT ( value, format [, culture ] )

Format function With Date SQL Server Example

SELECT @date1 AS 'UTCDate',
 FORMAT( @date1,'d','en-US') AS 'US Culture Date',
 FORMAT( @date1,'d','en-IN') AS 'INDIAN Culture Date'

I am using here all three parameter as i told you second parameter is case sensitive if you use 'D' instead of 'd' in second parameter then output will be different. In above query we select three columns first is by default utcdate second is us culture date format and last one is indian culture date format.Now lets take an Query to use second parameter as in Capital letter format 

SELECT @date AS 'UTCDate',
 FORMAT( @date,'D','en-US') AS 'US Culture Date',
 FORMAT( @date,'D','en-IN') AS 'INDIAN Culture Date'

Format function With Date Output

Format function SQL server example

Format function With Currency Example

DECLARE @Price1 INT = 250
SELECT FORMAT(@Price1,'c','en-US') 
         AS 'USDollar',       
         AS 'BritainPound'
DECLARE @Price DECIMAL(8,3) = 50.857
SELECT FORMAT( @Price, 'C') AS 'Default',
       FORMAT( @Price, 'C0') AS 'With 0 Decimal',
       FORMAT( @Price, 'C1') AS 'With 1 Decimal',
       FORMAT( @Price, 'C2') AS 'With 2 Decimal'
I using here only c0 to c2 in second query but you can use more than this.

Format function output With Currency Example

Convert Currency with format function

1 comment: