SQL Server 2012 - Using the FORMAT Function to Apply Masks and Formatting to Numbers and Dates

Views: 13.787
Reading Time: 6 minutes

Hello people,
Alright?

In this post today I would like to show you the T-SQL FORMAT function, available since SQL Server 2012, which until today few people use in day to day formatting of dates and numbers.

When I look at Queries, Functions, and Stored Procedures, I see that even today, many developers insist on using CAST, CONVERT, and concatenations to format dates and numbers, even with a function specifically for that. After reading this post, I hope you understand how to use this function and start simplifying your T-SQL codes with it.

Who never had to fill a number with 0 on the left to generate layout? Format a date? Extract time only from a date? There are numerous situations where the FORMAT function is useful.

Formatting Numeric Data

Predefined Formatting
Simpler use, predefined functions allow formatting of values ​​using masks already defined by default in SQL Server, such as “C” for currency models.

Most used formats

Examples

Other formats

Examples

Custom Formatting

Filling a number with leading zero

Formatting a Number for Brazilian Currency

Function used in this example:

Another examples:

Result:

Formatting dates

Predefined Formatting

Most common formats

Result:

Full table:

Format

Description

Examples

"d"

Short date pattern.

More information: Abbreviated Date Format Specifier ("d").

2009-06-15T13:45:30 -> 15/6/2009 (en-US)

2009-06-15T13:45:30 -> 15/06/2009 (fr-FR)

2009-06-06/2009/15 (ja-JP) -> 15T13:45:30

"D"

Full Date Pattern.

More information:Full Date Format Specifier ("D").

2009-06-15T13: 45: 30 -> Monday, 15 June 2009 (en-US)

2009-06-15T13: 45: 30 -> 15 г июня 2009. (ru-UK)

2009-06-15T13: 45: 30 -> Montag, 15. Juni 2009 (de-DE)

"f"

Complete date / time pattern (short time).

More information: Full Date and Short Time Format Specifier ("f").

2009-06-15T13: 45: 30 -> Monday, 15 June 2009: 1 PM (en-US)

2009-06-15T13: 45: 30 -> Join 15 2009 13: 45 (sv-SE)

2009-06-15T13: 45: 30 -> Δευτέρα, 15 Ιουνίου 2009 1: 45 μμ (el-GR)

"F"

Full date / time pattern (full time).

More information: Full Date and Full Time Format Specifier ("F").

2009-06-15T13: 45: 30 -> Monday, 15 June 2009: 1: 45 PM (en-US)

2009-06-15T13: 45: 30 -> Join 15 2009 13: 45: 30 (sv-SE)

2009-06-15T13: 45: 30 -> Δευτέρα, 15 Ιουνίου 2009 1: 45: 30 μμ (el-GR)

"g"

General pattern of date / time (abbreviated time).

More information: Abbreviated Date and Time General Format Specifier ("g").

2009-06-15T13:45:30 -> 15/6/2009 1:45 PM (en-US)

2009-06-15T13:45:30 -> 15/06/2009 13:45 (es-ES)

2009-06-15T13:45:30 -> 15/6/2009 13:45 (zh-CN)

"G"

General pattern of date / time (full time).

More information: General complete date and time format specifier ("G").

2009-06-15T13:45:30 -> 15/6/2009 1:45:30 PM (en-US)

2009-06-15T13:45:30 -> 15/06/2009 13:45:30 (es-ES)

2009-06-15T13:45:30 -> 15/6/2009 13:45:30 (zh-CN)

"M", "m"

Month / day pattern.

More information: Month Format Specifier ("M", "m").

2009-06-15T13: 45: 30 -> June 15 (en-US)

2009-06-15 -> 15T13: 45: 30. juni (da-dk)

2009-06-15T13: 45: 30 -> Juni 15 (id-ID)

"O", "o"

Round trip date / time pattern.

More information: Round trip format specifier ("O", "o").

Date Time values:

2009-06-15T13:45:30 (DateTimeKind)--> 2009-06-15T13:45:30.0000000-07:00

2009-06-15T13:45:30 (DateTimeKind)--> 2009-06-15T13:45:30.0000000Z

2009-06-15T13:45:30 (DateTimeKind)--> 2009-06-15T13:45:30.0000000

DateTimeOffset values:

2009-06-15T13:45:30-07:00--> 2009-06-15T13:45:30.0000000-07:00

"R", "r"

RFC1123 Standard

More information: RFC1123 Format Specifier ("R", "r").

2009-06-15T13: 45: 30 -> Monday, 15 June 2009 20: 45: 30 GMT

"s"

Sortable date / time pattern.

More information: Sortable format specifier ("s").

2009-06-15T13:45:30 (DateTimeKind) -> 2009-06-15T13:45:30

2009-06-15T13:45:30 (DateTimeKind) -> 2009-06-15T13:45:30

"t"

Abbreviated Time Pattern.

More information: Abbreviated Time Format Specifier ("t").

2009-06-15T13: 45: 30 -> 1: 45 PM (en-US)

2009-06-15T13:45:30 -> 13:45 (hr-HR)

2009-06-01: 45 -> 15T13: 45: 30 م (air-EG)

"T"

Full time pattern.

More information: Full Time Format Specifier ("T").

2009-06-15T13:45:30 -> 1:45:30 PM (en-US)

2009-06-15T13:45:30 -> 13:45:30 (hr-HR)

2009-06-01:45:30 -> 15T13:45:30 م (ar-EG)

"u"

Universal Classifiable Date / Time Pattern.

More information: Universal Sortable Pattern Format Specifier ("u").

With a Date Time valor: 2009-06-2009-06-15-> 15T13:45:30 13:45:30Z

With a DateTimeOffset valor: 2009-06-2009-06-15-> 15T13:45:30 20:45:30Z

"OR"

Universal full date / time pattern.

More information: Full Universal Pattern Format Specifier ("U").

2009-06-15T13: 45: 30 -> Monday, 15 June 2009: 8: 45 PM (en-US)

2009-06-15T13: 45: 30 -> Join 15 2009 20: 45: 30 (sv-SE)

2009-06-15T13: 45: 30 -> Δευτέρα, 15 Ιουνίου 2009 8: 45: 30 μμ (el-GR)

"Y", "y"

Default year month.

More information: Month Year Format Specifier ("Y").

2009-06-15T13: 45: 30 -> June of 2009 (en-US)

2009-06-15T13: 45: 30 -> Juni 2009 (da-DK)

2009-06-15T13: 45: 30 -> Juni 2009 (id-ID)

Any other single character

Unknown specifier.

Generates one FormatException of runtime.

Custom Formatting

Result:

Full table:

Format

Description

Examples

"d"

The day of the month, from 1 to 31.

More information: the custom format specifier "d".

2009-06-01T13:45:30 -> 1

2009-06-15T13:45:30 -> 15

"dd"

The day of the month, from 01 to 31.

More information: the custom format specifier "dd".

2009-06-01 -> 01T13:45:30

2009-06-15T13:45:30 -> 15

"ddd"

The abbreviated name of the day of the week.

More information: the custom format specifier "ddd".

2009-06-15T13: 45: 30 -> Mon

2009-06-15T13: 45: 30 -> Пн (ru-RU)

2009-06-15T13: 45: 30 -> lun. (fr-FR)

"dddd"

The full name of the day of the week.

More information: the custom format specifier "dddd".

2009-06-15T13: 45: 30 -> Second (en-US)

2009-06-15T13: 45: 30 -> понедельник (ru-RU)

2009-06-15T13: 45: 30 -> lundi (fr-FR)

"f"

The tenths of a second in a date and time value.

More information: the custom format specifier "f".

2009-06-15T13:45:30.6170000 -> 6

2009-06-15T13:45:30.05 -> 0

"ff"

The hundredths of a second in a date and time value.

More information: the custom format specifier "ff".

2009-06-15T13:45:30.6170000 -> 61

2009-06-00 -> 15T13:45:30.0050000

"fff"

The milliseconds in a date and time value.

More information: the custom format specifier "fff".

6/15/2009 13:45:30.617 -> 617

6/15/2009 13:45:30.0005 -> 000

"ffff"

The tenths of milliseconds in a date and time value.

More information: the custom format specifier "ffff".

2009-06-15T13:45:30.6175000 -> 6175

2009-06-0000 -> 15T13:45:30.0000500

"fffff"

The hundredths of milliseconds in a date and time value.

More information: the custom format specifier "fffff".

2009-06-15T13:45:30.6175400 -> 61754

6/15/2009 13:45:30.000005 -> 00000

"ffffff"

The millionths of a second in a date and time value.

More information: the custom format specifier "ffffff".

2009-06-15T13:45:30.6175420 -> 617542

2009-06-15T13:45:30.0000005 -> 000000

"fffffff"

The tenths of a millionths of a second in a date and time value.

More information: the custom format specifier "fffffff".

2009-06-15T13:45:30.6175425 -> 6175425

2009-06-15T13:45:30.0001150 -> 0001150

"F"

If nonzero, the tenths of a second in a date and time value.

More information: custom format specifier "F".

2009-06-15T13:45:30.6170000 -> 6

2009-06-15T13: 45: 30.0500000 -> (No Exit)

"FF"

If nonzero, the hundredths of a second in a date and time value.

More information: the custom format specifier "FF".

2009-06-15T13:45:30.6170000 -> 61

2009-06-15T13: 45: 30.0050000 -> (No Exit)

"FFF"

If nonzero, the milliseconds in a date and time value.

More information: the custom format specifier "FFF".

2009-06-15T13:45:30.6170000 -> 617

2009-06-15T13: 45: 30.0005000 -> (No Exit)

"FFFF"

If nonzero, the tenths of milliseconds in a date and time value.

More information: the custom format specifier "FFFF".

2009-06-15T13:45:30.5275000 -> 5275

2009-06-15T13: 45: 30.0000500 -> (No Exit)

"FFFFF"

If nonzero, the hundredths of milliseconds in a date and time value.

More information: the custom format specifier "FFFFF".

2009-06-15T13:45:30.6175400 -> 61754

2009-06-15T13: 45: 30.0000050 -> (No Exit)

"FFFFFF"

If nonzero, the millionths of a second in a date and time value.

More information: the custom format specifier "FFFFFF".

2009-06-15T13:45:30.6175420 -> 617542

2009-06-15T13: 45: 30.0000005 -> (No Exit)

"FFFFFFF"

If nonzero, the tenths of a millionths of a second in a date and time value.

More information: the custom format specifier "FFFFFFF".

2009-06-15T13:45:30.6175425 -> 6175425

2009-06-15T13:45:30.0001150 -> 000115

"g", "gg"

The period or era.

More information: "g" or "gg" custom format specifier.

2009-06-15T13:45:30.6170000 -> A.D.

"H"

The time using a clock from 12 hours from 1 to 12.

More information: the custom format specifier "h".

2009-06-15T01:45:30 -> 1

2009-06-15T13:45:30 -> 1

"hh"

The time using a clock from 12 hours from 01 to 12.

More information: the custom format specifier "hh".

2009-06-01 -> 15T01:45:30

2009-06-01 -> 15T13:45:30

"H"

The time using a clock from 24 hours from 0 to 23.

More information: custom format specifier "H".

2009-06-15T01:45:30 -> 1

2009-06-13 -> 15T13:45:30

"Hh"

The time using a clock from 24 hours from 00 to 23.

More information: the custom format specifier "HH".

2009-06-01 -> 15T01:45:30

2009-06-13 -> 15T13:45:30

"K"

Time zone information.

More information: the custom format specifier "K".

With Date Time values:

2009-06-15T13: 45: 30, what unspecified type ->

2009-06-15T13: 45: 30, Utc Type -> Z

2009-06-15T13: 45: 30, Type Local -> - 07: 00 (Depends on Local Computer Settings)

With DateTimeOffset values:

2009-06-15T01:45:30-07:00--> -07:00

2009-06-15T08:45:30 + 00:00--> + 00:00

"m"

The minute, from 0 to 59.

More information: the custom format specifier "m".

2009-06-15T01:09:30 -> 9

2009-06-29 -> 15T13:29:30

"mm"

The minute, from 00 to 59.

More information: the custom format specifier "mm".

2009-06-15T01:09:30 -> 09

2009-06-45 -> 15T01:45:30

"M"

The month, from 1 to 12.

More information: the custom format specifier "M".

2009-06-15T13:45:30 -> 6

"MM"

The month, from 01 to 12.

More information: the custom format specifier "MM".

2009-06-06 -> 15T13:45:30

"MMM"

The abbreviated name of the month.

More information: the custom format specifier "MMM".

2009-06-15T13: 45: 30 -> Jun (en-US)

2009-06-15T13: 45: 30 -> Juin (fr-FR)

2009-06-15T13: 45: 30 -> Jun (zu-ZA)

"MMMM"

The full name of the month.

More information: the custom format specifier "MMMM".

2009-06-15T13: 45: 30 -> June (en-US)

2009-06-15T13: 45: 30 -> Juni (da-DK)

2009-06-15T13: 45: 30 -> uJuni (zu-ZA)

"s"

The second, from 0 to 59.

More information: the custom format specifier "s".

2009-06-15T13:45:09 -> 9

"ss"

The second, from 00 to 59.

More information: the custom format specifier "ss".

2009-06-15T13:45:09 -> 09

"t"

The first character of the AM / PM designator.

More information: the custom format specifier "t".

2009-06-15T13: 45: 30 -> P (en-US)

2009-06-15T13: 45: 30 -> 午 (ja-JP)

2009-06-15T13:45:30 -> (fr-FR)

"tt"

The AM / PM designator.

More information: the custom format specifier "tt".

2009-06-15T13: 45: 30 -> PM (en-US)

2009-06-15T13: 45: 30 -> 午後 (ja-JP)

2009-06-15T13:45:30 -> (fr-FR)

"y"

The year, from 0 to 99.

More information: the custom format specifier "y".

0001-01-01T00:00:00 -> 1

0900-01-01T00:00:00 -> 0

1900-01-01T00:00:00 -> 0

2009-06-15T13:45:30 -> 9

2019-06-19 -> 15T13:45:30

"AA"

The year, from 00 to 99.

More information: the custom format specifier "yy".

0001-01-01 -> 01T00:00:00

0900-01-00 -> 01T00:00:00

1900-01-00 -> 01T00:00:00

2019-06-19 -> 15T13:45:30

"yyy"

The year, with a minimum of three digits.

More information: the custom format specifier "yyy".

0001-01-01T00:00:00 -> 001

0900-01-01T00:00:00 -> 900

1900-01-1900 -> 01T00:00:00

2009-06-2009 -> 15T13:45:30

"yyyy"

The year as a four digit number.

More information: the custom format specifier "yyyy".

0001-01-01T00:00:00 -> 0001

0900-01-01T00:00:00 -> 0900

1900-01-1900 -> 01T00:00:00

2009-06-2009 -> 15T13:45:30

"yyyyy"

The year as a five digit number.

More information: the custom format specifier "yyyyy".

0001-01-01T00:00:00 -> 00001

2009-06-15T13:45:30 -> 02009

"z"

UTC time difference, no leading zeros.

More information: the custom format specifier "z".

2009-06-15T13:45:30-07:00 -> -7

"zz"

UTC time difference, with a leading zero of a single digit value.

More information: the custom format specifier "zz".

2009-06-15T13:45:30-07:00->-07

"zzz"

Hours and minutes UTC offset.

More information: the custom format specifier "zzz".

2009-06-15T13:45:30-07:00->-07:00

""

The time separator.

More information: the ":" custom format specifier.

2009-06--> 15T13:45:30: (en-US)

2009-06--> 15T13:45:30. (it-IT)

2009-06--> 15T13:45:30: (ja-JP)

" or "

The date separator.

More information: the custom format specifier "/".

2009-06-15T13: 45: 30 -> / (en-US)

2009-06 -> 15T13: 45: 30 - (ar-DZ)

2009-06--> 15T13:45:30. (tr-TR)

"sequence"

'sequence'

Literal string delimiter.

More information: Character Literals.

2009-06-15T13: 45: 30 ("arr:" h: mt) -> arr: 1: 45 P

2009-06-15T13: 45: 30 ('Arr:' h: mt) -> Arr: 1: 45 P

%

Defines the following character as a custom format specifier.

More information:using simple custom format specifiers.

2009-06-15T13:45:30 (%h) -> 1

\

The escape character.

More information: Character Literals e using the escape character.

2009-06-15T13: 45: 30 (h \ h) -> 1 h

Any other character

The character is copied to the unchanged result string.

More information: Character Literals.

2009-06-15T01: 45: 30 (Arr hh: mm t) -> Arr 01: 45 a

Formatting Numbers and Dates Before SQL Server 2012

Now that you've seen how simple and fast it is to format numbers and dates using the native FORMAT function, it's even hard to want to manually format these data types again, right?

Unfortunately, the FORMAT function was introduced in SQL Server 2012, that is, in 2005 and 2008 versions, you still have to use the traditional (costly and laborious) formatting of dates and numbers. Or not.

For those who have a database project in their instance (also known as SQLCLR), you can easily implement very similar (almost the same) XMUMX functions as the FORMAT function, allowing you to easily format data using these functions, even in 2 and 2005 versions of 2008. SQL Server

If you do not know SQLCLR, or do not know how to create your first project, read the post Introduction to SQL Common Language Runtime (CLR) in SQL Server.

How to format dates with CLR

See how the usage is practically the same as the FORMAT function (I didn't implement the third parameter - culture):

Note that both predefined and custom formats remain unchanged using the CLR function. This is because the FORMAT function internally uses the same C # function that I used in these SQLCLR functions.

FncFormata_Datetime Source Code

How to format numbers with CLR

Examples with standard formats

Custom Format Examples

FncFormata_Numero source code

That's it folks!
I hope that you will use the FORMAT function more in your daily life (when necessary) and if you are using SQL Server versions 2005 or 2008, know that it is possible to simulate the behavior of this function using SQLCLR.

A big hug and even more.

References:
FORMAT (Transact-SQL)
Formatting Types in the .NET Framework
Standard numeric format strings
Custom numeric format strings
Default date and time format strings
Custom DateTime Format Strings