SQL - FORMAT() Function

The SQL format() function is used to retrieve the formatted value of the given text. It accepts three parameters, representing value, format, and culture where, the culture parameter is completely optional.

This function returns a value formatted with the specified format and optional culture. Using this function we can also change the format of date & time values, numerical values or any other values. But for general data type conversions, SQL has the CAST() or CONVERT() functions.

Syntax

Following is the syntax of the SQL FORMAT() function −

FORMAT( value, format [, culture ] )

Parameters

  • value − String value we need to format.

  • format − The patterns in which we need to format the string.

Example

The following SELECT query converts the date to the specified format, This Query will display the specified date format −

DECLARE @DATE DATE = '04/20/2022'; 
SELECT FORMAT(@DATE, 'MM/dd/yyyy', 'EN-US') AS 'DATE';

Output

Following is the output of the above query −

+-------------+
|   DATE      |
+-------------+
| 04/20/2022  |
+-------------+

Example

The following SELECT query can also have the current date format, This Query will display the current date format −

SELECT FORMAT(@CUR_DATE, 'MM/dd/yyyy', 'EN-US') AS 'CURRENT_DATE';

Output

The above SQL query produces the following output −

+---------------+
| CURRENT_DATE  |
+---------------+
| 02/20/2022    |
+---------------+

Example

The following SELECT query converts a string into a custom string format −

SELECT FORMAT(146597, ' $#,##,###.00 ') AS Custom;

Output

On executing the above query, it will produce the following output −

+--------------+
| Custom       |
+--------------+
| $146,597.00  |
+--------------+

Example

The following SELECT query converts the time to the specified format −

SELECT FORMAT(CAST('2018-01-01 01:00' AS datetime2), N'hh:mm tt') AS 'CURRENT_TIME';

Output

On executing the above query, it will produce the following output −

+--------------+
| CURRENT_TIME |
+--------------+
|   1:00 AM    |
+--------------+

Example

The following SELECT query converts the string to percentages −

SELECT FORMAT((10/10), 'p' ) AS [Percentage];

Output

On executing the above query, it will produce the following output −

+--------------+
| PERCENTAGE   |
+--------------+
|   100.00%    |
+--------------+

Example

The following SELECT query turns a string into a currency amount −

SELECT FORMAT(999, 'C' ) AS Currency;

Output

On executing the above query, it will produce the following output −

+--------------+
| CURRENCY     |
+--------------+
|   $999.00    |
+--------------+

Example

You can pass the table column as an argument to the FORMAT() function which changes the into a specified format. Assume we have created a table with the name Customers using the CREATE statement as follows −

create table CUSTOMERS(
   ID INT NOT NULL, 
   NAME VARCHAR(15) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR(25), 
   SALARY DECIMAL(10, 4), PRIMARY KEY(ID)
);

Now let's insert seven records into the customers table using the INSERT statement as follows:−

insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(6, 'Komal', 22, 'MP', 4500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(7, 'Muffy', 24, 'Indore', 10000.00);

The following SELECT query uses the FORMAT function with the SALARY column from the above CUSTOMERS table −

SELECT ID, FORMAT(SALARY, 'C') AS CURRENCY FROM CUSTOMERS;

Output

After executing the above statement, it produces the following output −

+----------+-----------+
|   ID     | CURRENCY  |
+----------+-----------+
|    1     | $2,000.00 |
|    2     | $1,500.00 |  
|    3     | $2,000.00 |
|    4     | $6,500.00 |
|    5     | $8,500.00 |
|    6     | $4,500.00 |
|    7     |$10,000.00 |
+----------+-----------+
sql-string-functions.htm
Advertisements