The SQL Format is one of the String Function, which is used to format the specified value in given format. In this article we will show you, How to use FORMAT in SQL Server with example.
SQL Format Syntax
The basic syntax of the Format Function in SQL Server is as shown below:
|
1 |
Format(Value, Format, Culture) |
- Value: Please specify a valid Expression of supporting Data Type.
- Format: Please specify a valid .NET Framework format string.
- Culture: This is an optional argument.
SQL Format – Formatting Date
In this example we first declared a Datetime variable and assigned GETDATE() to it. Here we are going to use the FORMAT function to return the date in different formats. I suggest you to refer the Standard Date and Time Format Strings article to understand the string formats that we used in this example.
|
1 2 3 4 5 6 7 8 9 10 11 |
--Using FORMAT in SQL Server to Format Date DECLARE @Vardate DATETIME = GETDATE() SELECT FORMAT(@Vardate, 'd', 'en-US' ) AS 'Result 1', FORMAT(@Vardate, 'D', 'en-US' ) AS 'Result 2' SELECT FORMAT(@Vardate, 'f', 'en-US' ) AS 'Result 3', FORMAT(@Vardate, 'F', 'en-US' ) AS 'Result 4' SELECT FORMAT(@Vardate, 'g', 'en-US' ) AS 'Result 5', FORMAT(@Vardate, 'G', 'en-US' ) AS 'Result 6' SELECT FORMAT(@Vardate, 'm', 'en-US' ) AS 'Result 7', FORMAT(@Vardate, 'M', 'en-US' ) AS 'Result 8' SELECT FORMAT(@Vardate, 'O', 'en-US' ) AS 'Result 9', FORMAT(@Vardate, 'R', 'en-US' ) AS 'Result 10' SELECT FORMAT(@Vardate, 's', 'en-US' ) AS 'Result 11', FORMAT(@Vardate, 'S', 'en-US' ) AS 'Result 12' SELECT FORMAT(@Vardate, 't', 'en-US' ) AS 'Result 13', FORMAT(@Vardate, 'T', 'en-US' ) AS 'Result 14' SELECT FORMAT(@Vardate, 'u', 'en-US' ) AS 'Result 15', FORMAT(@Vardate, 'U', 'en-US' ) AS 'Result 16' SELECT FORMAT(@Vardate, 'Y', 'en-US' ) AS 'Result 17' |
OUTPUT
SQL Server Format – Formatting Date using Culture
In this example we are going to use the third argument culture.
|
1 2 3 4 5 6 7 8 |
--Using FORMAT in SQL Server to Format Date (Culture) DECLARE @Vardate DATETIME = GETDATE() SELECT FORMAT(@Vardate, 'dd', 'en-US' ) AS 'Result 1', FORMAT(@Vardate, 'dddd', 'hi-IN' ) AS 'Result 2' SELECT FORMAT(@Vardate, 'd', 'de-DE' ) AS 'Result 3', FORMAT(@Vardate, 'dddd', 'ru-RU' ) AS 'Result 4' SELECT FORMAT(@Vardate, 'M', 'en-US' ) AS 'Result 5', FORMAT(@Vardate, 'MMMM', 'hi-IN' ) AS 'Result 6' SELECT FORMAT(@Vardate, 'MM', 'de-DE' ) AS 'Result 7', FORMAT(@Vardate, 'MMMM', 'ru-RU' ) AS 'Result 8' SELECT FORMAT(@Vardate, 'yy', 'en-US' ) AS 'Result 9', FORMAT(@Vardate, 'y', 'hi-IN' ) AS 'Result 10' SELECT FORMAT(@Vardate, 'yyyy', 'de-DE' ) AS 'Result 11', FORMAT(@Vardate, 'y', 'ru-RU' ) AS 'Result 12' |
OUTPUT
SQL Format – Formatting Date in Custom Format
In this example we are going to define custom date time formats.
|
1 2 3 4 5 6 7 8 |
--Using FORMAT in SQL Server to Format Date in Custom Format DECLARE @Vardate DATETIME = GETDATE() SELECT FORMAT(@Vardate, 'dd/mm/yyyy') AS 'date in dd/mm/yyyy Format' SELECT FORMAT(@Vardate, 'dd/mm/yy') AS 'date in dd/mm/yy Format' SELECT FORMAT(@Vardate, 'd/m/yy') AS 'date in d/m/yy Format' SELECT FORMAT(@Vardate, 'mm/dd/yyyy') AS 'date in mm/dd/yyyy Format' SELECT FORMAT(@Vardate, 'MMMM dddd yyyy hh:mm:ss:mmmm') AS 'date in MMMM dddd yyyy hh:mm:ss:mmmm Format' SELECT FORMAT(@Vardate, 'MMMM yyyy, dddd hh:mm:ss:mmmm') AS 'date in MMMM yyyy, dddd hh:mm:ss:mmmm Format' |
OUTPUT
SQL Format – Formatting Currency using Culture
In this example we are going to format the Currency values based on the specified culture.
|
1 2 3 4 5 6 |
--Using FORMAT in SQL Server to Format Currency Culture DECLARE @Sales INT = 3325 SELECT FORMAT(@Sales, 'c', 'en-US' ) AS 'USA Currency' SELECT FORMAT(@Sales, 'c', 'ru-RU' ) AS 'Russian Currency' SELECT FORMAT(@Sales, 'c', 'hi-IN' ) AS 'Indian Currency' SELECT FORMAT(@Sales, 'c', 'de-DE' ) AS 'Indian Currency' |
OUTPUT
SQL Format – Formatting Currency
In this example we are going to format the Currency.
|
1 2 3 4 5 6 7 8 |
--Using FORMAT in SQL Server to Format Decimal Values in Currency DECLARE @Sales DECIMAL(8, 4) = 3325.2569 SELECT FORMAT(@Sales, 'c' ) AS 'Result 1' SELECT FORMAT(@Sales, 'c0' ) AS 'Result 2' SELECT FORMAT(@Sales, 'c1' ) AS 'Result 3' SELECT FORMAT(@Sales, 'c2' ) AS 'Result 4' SELECT FORMAT(@Sales, 'c3' ) AS 'Result 5' SELECT FORMAT(@Sales, 'c4' ) AS 'Result 6' |
OUTPUT
SQL Format – Formatting Decimals in Currency
In this example we are going to format the decimal values in Currency.
|
1 2 3 4 5 6 7 8 |
--Using FORMAT in SQL Server to Format Decimal Values in Currency DECLARE @Sales DECIMAL(8, 4) = 3325.2569 SELECT FORMAT(@Sales, 'c', 'en-US' ) AS 'Result 1' SELECT FORMAT(@Sales, 'c0', 'hi-IN' ) AS 'Result 2' SELECT FORMAT(@Sales, 'c1', 'ru-RU') AS 'Result 3' SELECT FORMAT(@Sales, 'c2', 'fr-FR' ) AS 'Result 4' SELECT FORMAT(@Sales, 'c3', 'de-DE') AS 'Result 5' SELECT FORMAT(@Sales, 'c4', 'zh-CN') AS 'Result 6' |
OUTPUT
SQL Format – Formatting Numbers using Culture
In this example we are going to use the FORMAT function to format Numbers
|
1 2 3 4 5 6 7 8 |
--Using FORMAT in SQL Server to Format Numbers DECLARE @Number DECIMAL(11, 4) = 1453325.2569 SELECT FORMAT(@Number, 'N', 'en-US') AS 'Result 1' SELECT FORMAT(@Number, 'N', 'en-IN' ) AS 'Result 2' SELECT FORMAT(@Number, 'N', 'ru-RU') AS 'Result 3' SELECT FORMAT(@Number, 'N', 'fr-FR' ) AS 'Result 4' SELECT FORMAT(@Number, 'N', 'de-DE') AS 'Result 5' SELECT FORMAT(@Number, 'N', 'zh-CN') AS 'Result 6' |
OUTPUT
SQL Server Format – Formatting Numbers
In this example we are going to format numbers
|
1 2 3 4 5 6 7 8 |
--Using FORMAT in SQL Server to Format Numbers DECLARE @Number DECIMAL(10,4) = 945354.1295 DECLARE @Num INT = 945332534 SELECT FORMAT(@Number, '#') AS 'Result 1' SELECT FORMAT(@Number, '#,###' ) AS 'Result 2' SELECT FORMAT(@Number, '#.00') AS 'Result 3' SELECT FORMAT(@Number, '#,###.000' ) AS 'Result 4' SELECT FORMAT(@Num, '###-##-####') AS 'Result 5' |
OUTPUT
Thank You for Visiting Our Blog
Leave a Reply