SQL Format

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.

--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.

--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.

--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.

--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.

--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.

--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

--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

--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

SQL Format Syntax

SQL Format – Formatting Date

SQL Server Format – Formatting Date using Culture

SQL Format – Formatting Date in Custom Format

SQL Format – Formatting Currency using Culture

SQL Format – Formatting Currency

SQL Format – Formatting Decimals in Currency

SQL Format – Formatting Numbers using Culture

SQL Server Format – Formatting Numbers

Share your Feedback, or Code!! Cancel reply