FORMAT() Function in SQL Server
Last Updated :
04 Oct, 2021
The FORMAT() function is one of the String Functions, which is used to format the specified value in the given format.
Syntax :
FORMAT(value, format, culture)
Parameter:
This method accepts three parameters as mentioned above and described below:
- Value: It is the value to do formatting. It should be in support of the data type format.
- Format: It is the required format in which we require the output.
- Culture: It is an optional parameter. By default, SQL Server uses the current session language for a default culture. We can provide a specific culture here, but the .Net framework should support it. We get an error message in case of invalid Culture
Returns: It returns a value formatted with the specified format and optional culture.
Now if we want to Format a Number. then we use the below query:
Query:
SELECT FORMAT(25, 'N')
Output :
Take another example of PERCENTAGE format.
Query:
SELECT FORMAT(1, 'P', 'en-US')AS [PERCENTAGE IN US FORMAT],
FORMAT(1, 'P', 'en-IN') AS [PERCENTAGE IN INDIA FORMAT];
Output :
Example of DATE format.
Query:
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result'
Output :
In this example, we will format the current time with AM or PM.
Query:
SELECT FORMAT(SYSDATETIME(), N'hh:mm tt');
Output :
In this example, we change the CURRENCY format.
Query:
SELECT
FORMAT(1, 'C', 'in-IN') AS 'INDIA',
FORMAT(1, 'C', 'ch-CH') AS 'CHINA',
FORMAT(1, 'C', 'sw-SW') AS 'SWITZERLAND',
FORMAT(1, 'C', 'us-US') AS 'USA';
Output :
Share your thoughts in the comments
Please Login to comment...