FORMAT ( Transact-SQL)

 

Brief Description:      Returns a value formatted with the specified format and optional culture

Syntax:                       FORMAT ( value, format [, culture ] )

Availability:               All Versions of SQL Server 2012

Details:           Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general type conversions, continue to use CAST or CONVERT.

In the case of errors other than a culture that is not valid, such as a format that is not valid, FORMAT returns a null.

FORMAT relies on the presence of .the .NET Framework Common Language Runtime (CLR).

This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Acceptable data types for the value argument are listed in the following table, together with their .NET Framework mapping information:

Category	Type	.NET type
Numeric	bigint	Int64
Numeric	int	Int32
Numeric	smallint	Int16
Numeric	tinyint	Byte
Numeric	decimal	SqlDecimal
Numeric	numeric	SqlDecimal
Numeric	float	Double
Numeric	real	Single
Numeric	smallmoney	Decimal
Numeric	money	Decimal
Date and Time	date	DateTime
Date and Time	time	TimeSpan
Date and Time	datetime	DateTime
Date and Time	smalldatetime	DateTime
Date and Time	datetime2	DateTime
Date and Time	datetimeoffset	DateTimeOffset

 

Examples:

1.   

 

declare @d datetime = getdate();

select format( @d, 'dd/MM/yyyy', 'en-US' ) as fomatteddate

 

output

---------------

fomatteddate

01/04/2012

 

  2.

declare @a int = 1234

select format(@a, '0000000000') AS foramt_a

go

 

output

-----------

foramt_a

0000001234

 

 

Pros:  

 

·         Formatting Numeric values with prefix zeros

·         Formatting Dates on different cultures