Home > Denali, String Functions, TSQL > SQL Server Denali – FORMAT() string function

SQL Server Denali – FORMAT() string function

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.

Advertisements
Categories: Denali, String Functions, TSQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: