How to format a numeric value or date time value to a customized format?

Problem: How to format a numeric value or date time value to a customized format?

Solution: FORMAT, Starting from SQL Server 2012 this string function is available to use.

Format is a string function which can style a date time or numeric value to specified style you want to apply.

Returns a value formatted with the specified format and optional culture in SQL Server 2016. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.

Syntax:

FORMAT (value, format [, culture]) 

To see a list of formatted strings which are supported. Please go through this Formatting Types

Example 1:

In this example, I would like to show we can format a phone number to have a specific format and a date to a style.

Declare @phone int =1234567890

SELECT @phone as  originalformat,FORMAT(@phone,'(###)-###-####') as formattedphonenumber

OUTPUT:

originalformat	formattedphonenumber
1234567890	(123)-456-7890

Example 2:

In this example, I would like to format the order details into a presentable format like adding currency, date style.

Declare @orderdate datetime =getdate()

, @orderlocation varchar(100) ='Europe'

, @orderprice decimal(16,9)='1560.65'

SELECT @orderdate as orderplaced,

 FORMAT(@orderdate,'d','en-gb') as orderdatebasedonlocation,

 @orderprice as orderedprice,

 FORMAT(@orderprice,'C') as formattedprice

OUTPUT:

orderplaced	orderdatebasedonlocation	orderedprice	formattedprice
2017-04-12 21:54:07.600	12/04/2017	1560.650000000	$1,560.65