SQL Server Denali brings us new string function, FORMAT function. It will make formatting of date/time and number values easier.

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

Formatting Date/Time

Let’s see how it works:

DECLARE @a datetime = getdate()
-- If the culture argument is not provided,
-- then the language of the current session is used.
SELECT FORMAT(@a, N'd') AS d,
FORMAT(@a, N'dd') AS dd,
FORMAT(@a, N'ddd') AS ddd,
FORMAT(@a, N'dddd') AS dddd

SELECT FORMAT(@a, N'M') AS M,
FORMAT(@a, N'MM') AS MM,
FORMAT(@a, N'MMM') AS MMM,
FORMAT(@a, N'MMMM') AS MMMM

SELECT FORMAT(@a, N'y') AS y,
FORMAT(@a, N'yy') AS yy,
FORMAT(@a, N'yyy') AS yyy

If you specify culture argument, you can easily format date to other languages. Next examples show you how to format date, weekday and month to Croatian language.

-- Formatting with Croatian language
SELECT FORMAT(@a, N'd', N'hr-HR') AS d,
FORMAT(@a, N'dd', N'hr-HR') AS dd,
FORMAT(@a, N'ddd', N'hr-HR') AS ddd,
FORMAT(@a, N'dddd', N'hr-HR') AS dddd

SELECT FORMAT(@a, 'M','hr-HR') AS M,
FORMAT(@a, 'MM','hr-HR') AS MM,
FORMAT(@a, 'MMM','hr-HR') AS MMM,
FORMAT(@a, 'MMMM','hr-HR') AS MMMM

Examples with German and Italian languages.

-- Formatting with German language
SELECT FORMAT(@a, N'd', N'de-DE') AS d,
FORMAT(@a, N'dd', N'de-DE') AS dd,
FORMAT(@a, N'ddd', N'de-DE') AS ddd,
FORMAT(@a, N'dddd', N'de-DE') AS dddd

-- Formatting with Italian language
SELECT FORMAT(@a, N'M', N'it-IT') AS M,
FORMAT(@a, N'MM', N'it-IT') AS MM,
FORMAT(@a, N'MMM', N'it-IT') AS MMM,
FORMAT(@a, N'MMMM', N'it-IT') AS MMMM

List of all available languages you can find in National Language Support (NLS) API Reference. With FORMAT function you can also use custom formatting. Let’s see how custom formatting works.

DECLARE @a datetime = getdate()

-- Custom formatting of date
SELECT FORMAT ( @a, N'dd/MM/yyyy') AS 'dd/MM/yyyy',
FORMAT ( @a, N'dd.MM.yyy')  AS 'dd.MM.yyy',
FORMAT ( @a, N'dd.MMMM.yy', 'hr-HR') AS 'dd.MMMM.yy Croatian',
FORMAT ( @a, N'dd.MMMM.yyy (dddd)', 'hr-HR') AS 'dd.MMMM.yyy (dddd) Croatian',
FORMAT ( @a, N'dddd, dd.MMM.yy', 'hr-HR') AS 'dddd, dd.MMM.yy Croatian',
FORMAT ( @a, N'dddd MMMM dd, yyyy', 'en-US') AS 'MMMM dd, yyyy (dddd) English'

-- Custom formatting of time
SELECT FORMAT ( @a, N'hh:mm:ss') AS 'hh:mm:ss',
FORMAT ( @a, N'hh:mm') AS 'hh:mm',
FORMAT ( @a, N'hh') AS 'hh',
FORMAT ( @a, N'mm') AS 'mm',
FORMAT ( @a, N'ss') AS 'ss'

Formatting Number

FORMAT function is very useful if you need to format currency value. Here are some examples for formatting currency value.

DECLARE @a numeric(10,2)
SET @a = '5132.45'
SELECT FORMAT(@a, N'c') AS SessionLanguage,
FORMAT(@a, N'c', 'hr-Hr') AS Croatian,
FORMAT(@a, N'c', 'de-DE') AS German,
FORMAT(@a, N'c', 'it-IT') AS Italian

-- You can also control number of digits after the decimal point
SELECT FORMAT(@a, N'c0', 'de-DE') AS German0,
FORMAT(@a, N'c1', 'de-DE') AS German1,
FORMAT(@a, N'c2', 'de-DE') AS German2,
FORMAT(@a, N'c3', 'de-DE') AS German3

With FORMAT function you can even format value to scientific and hexadecimal.

DECLARE @I int = 90
SELECT FORMAT(@I,'e') AS Scientific,
FORMAT(@I,'x') AS Hexa,	
FORMAT(@I,'x4') AS Hexa1

Format function is nice addition to the T-SQL language and it will make formatting much easier. Various tricks with CAST and CONVERT functions will no longer be required.