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

Views: 25.547 views

In this article

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, pre-defined functions allow formatting values using masks already defined by default in SQL Server, such as "C" for models (currency).

Most used formats

SELECT
    FORMAT(123456.99, 'C'), -- Formato de moeda padrão
    FORMAT(-123456.987654321, 'C4'), -- Formato de moeda com 4 casas decimais
    FORMAT(123456.987654321, 'C2', 'pt-br') -- Formato de moeda forçando a localidade pra Brasil e 2 casas decimais

SELECT
    FORMAT(123456.99, 'D'), -- Formato de número inteiro com valores numeric (NULL)
    FORMAT(123456, 'D'), -- Formato de número inteiro
    FORMAT(-123456, 'D4'), -- Formato de número inteiro com valores negativos
    FORMAT(123456, 'D10', 'pt-br'), -- formato de número inteiro com tamanho fixo em 10 caracteres
    FORMAT(-123456, 'D10', 'pt-br') -- formato de número inteiro com tamanho fixo em 10 caracteres

SELECT
    FORMAT(123456.99, 'E'), -- Formato de notação científica
    FORMAT(123456.99, 'E4') -- Formato de notação científica e 4 casas decimais de precisão

SELECT
    FORMAT(1, 'P'), -- Formato de porcentagem
    FORMAT(1, 'P2'), -- Formato de porcentagem com 2 casas decimais
    FORMAT(0.91, 'P'), -- Formato de porcentagem
    FORMAT(0.005, 'P4') -- Formato de porcentagem com 4 casas decimais

SELECT
    FORMAT(255, 'X'), -- Formato hexadecimal
    FORMAT(512, 'X8') -- Formato hexadecimal fixando o retorno em 8 caracteres

SELECT

FORMAT(123456.99, 'C'), -- Formato de moeda padrão

FORMAT(-123456.987654321, 'C4'), -- Formato de moeda com 4 casas decimais

FORMAT(123456.987654321, 'C2', 'pt-br') -- Formato de moeda forçando a localidade pra Brasil e 2 casas decimais

SELECT

FORMAT(123456.99, 'D'), -- Formato de número inteiro com valores numeric (NULL)

FORMAT(123456, 'D'), -- Formato de número inteiro

FORMAT(-123456, 'D4'), -- Formato de número inteiro com valores negativos

FORMAT(123456, 'D10', 'pt-br'), -- formato de número inteiro com tamanho fixo em 10 caracteres

FORMAT(-123456, 'D10', 'pt-br') -- formato de número inteiro com tamanho fixo em 10 caracteres

SELECT

FORMAT(123456.99, 'E'), -- Formato de notação científica

FORMAT(123456.99, 'E4') -- Formato de notação científica e 4 casas decimais de precisão

SELECT

FORMAT(1, 'P'), -- Formato de porcentagem

FORMAT(1, 'P2'), -- Formato de porcentagem com 2 casas decimais

FORMAT(0.91, 'P'), -- Formato de porcentagem

FORMAT(0.005, 'P4') -- Formato de porcentagem com 4 casas decimais

SELECT

FORMAT(255, 'X'), -- Formato hexadecimal

FORMAT(512, 'X8') -- Formato hexadecimal fixando o retorno em 8 caracteres

Examples

Other formats

SELECT
    FORMAT(123456.99, 'F'), -- Formato de número
    FORMAT(123456.99, 'F4') -- Formato de número com 4 casas decimais

SELECT
    FORMAT(123456.99, 'G'), -- Formato de número compacto
    FORMAT(123456.99, 'G4'), -- Formato de número (tenta forçar o número com 4 caracteres)
    FORMAT(123456.99, 'G20') -- Formato de número com tamanho máximo de 10 caracteres

SELECT
    FORMAT(123456.99, 'N'), -- Formato de número genérico
    FORMAT(123456.99, 'N4') -- Formato de número genérico fixando 4 casas decimais

SELECT

FORMAT(123456.99, 'F'), -- Formato de número

FORMAT(123456.99, 'F4') -- Formato de número com 4 casas decimais

SELECT

FORMAT(123456.99, 'G'), -- Formato de número compacto

FORMAT(123456.99, 'G4'), -- Formato de número (tenta forçar o número com 4 caracteres)

FORMAT(123456.99, 'G20') -- Formato de número com tamanho máximo de 10 caracteres

SELECT

FORMAT(123456.99, 'N'), -- Formato de número genérico

FORMAT(123456.99, 'N4') -- Formato de número genérico fixando 4 casas decimais

Examples

Custom Formatting

Filling a number with leading zero

Formatting a Number for Brazilian Currency

Function used in this example:

CREATE FUNCTION fncConverte_Moeda_Real (
    @Numero VARCHAR(40)
)
RETURNS VARCHAR(40)
AS 
BEGIN

    DECLARE @Contador INT
    DECLARE @Pontos INT
    DECLARE @CasasVirgula INT
    DECLARE @Parte1 VARCHAR(40)
    DECLARE @Parte2 VARCHAR(40)


    SET @CasasVirgula = (LEN(SUBSTRING(@Numero, CHARINDEX('.', @Numero) + 1, LEN(@Numero))))


    IF @CasasVirgula > 2
        SET @Numero = (SUBSTRING(@Numero, 1, (CHARINDEX('.', @Numero)) + 2))


    IF CHARINDEX('.', @Numero) <> 0 BEGIN
        SET @Numero = REPLACE(@Numero, '.', ',')


        IF LEN(SUBSTRING(@Numero, CHARINDEX(',', @Numero) + 1, 2)) = 1
            SET @Numero = @Numero + '0'
    END
    ELSE
        SET @Numero = @Numero + ',00'


    SET @Contador = (CHARINDEX(',', @Numero) - 1)
    SET @Pontos = @Contador / 3


    WHILE @Pontos <> 0 BEGIN
        SET @Contador = @Contador - 3
        SET @Parte2 = (SUBSTRING(@Numero, @Contador + 1, LEN(@Numero)))
        SET @Parte1 = (SUBSTRING(@Numero, 1, (CHARINDEX(@Parte2, @Numero) - 1)))
        SET @Numero = @Parte1 + '.' + @Parte2
        SET @Pontos = @Pontos - 1
    END


    IF (SUBSTRING(@Numero, 1, 1)) = '.'
        SET @Numero = (SUBSTRING(@Numero, 2, LEN(@Numero)))


    SET @Numero = 'R$ ' + @Numero


    RETURN @Numero

END

CREATE FUNCTION fncConverte_Moeda_Real (

@Numero VARCHAR(40)

)

RETURNS VARCHAR(40)

BEGIN

DECLARE @Contador INT

DECLARE @Pontos INT

DECLARE @CasasVirgula INT

DECLARE @Parte1 VARCHAR(40)

DECLARE @Parte2 VARCHAR(40)

SET @CasasVirgula = (LEN(SUBSTRING(@Numero, CHARINDEX('.', @Numero) + 1, LEN(@Numero))))

IF @CasasVirgula > 2

SET @Numero = (SUBSTRING(@Numero, 1, (CHARINDEX('.', @Numero)) + 2))

IF CHARINDEX('.', @Numero) <> 0 BEGIN

SET @Numero = REPLACE(@Numero, '.', ',')

IF LEN(SUBSTRING(@Numero, CHARINDEX(',', @Numero) + 1, 2)) = 1

SET @Numero = @Numero + '0'

END

ELSE

SET @Numero = @Numero + ',00'

SET @Contador = (CHARINDEX(',', @Numero) - 1)

SET @Pontos = @Contador / 3

WHILE @Pontos <> 0 BEGIN

SET @Contador = @Contador - 3

SET @Parte2 = (SUBSTRING(@Numero, @Contador + 1, LEN(@Numero)))

SET @Parte1 = (SUBSTRING(@Numero, 1, (CHARINDEX(@Parte2, @Numero) - 1)))

SET @Numero = @Parte1 + '.' + @Parte2

SET @Pontos = @Pontos - 1

END

IF (SUBSTRING(@Numero, 1, 1)) = '.'

SET @Numero = (SUBSTRING(@Numero, 2, LEN(@Numero)))

SET @Numero = 'R$ ' + @Numero

RETURN @Numero

END

Another examples:

SELECT
    -- Formato de moeda brasileira (manualmente)
    FORMAT(123456789.9, 'R$ ###,###,###,###.00'),
    -- Utilizando sessão (;) para formatar valores positivos e negativos
    FORMAT(123456789.9, 'R$ ###,###,###,###.00;-R$ ###,###,###,###.00'), 
    
    -- Utilizando sessão (;) para formatar valores positivos e negativos
    FORMAT(-123456789.9, 'R$ ###,###,###,###.00;-R$ ###,###,###,###.00'), 
    -- Utilizando sessão (;) para formatar valores positivos e negativos
    FORMAT(-123456789.9, 'R$ ###,###,###,###.00;(R$ ###,###,###,###.00)'),
    
    -- Formatando porcentagem com 2 casas decimais
    FORMAT(0.9975, '#.00%'), 
    -- Formatando porcentagem com 4 casas decimais
    FORMAT(0.997521654, '#.0000%'),
    -- Formatando porcentagem com 4 casas decimais
    FORMAT(123456789.997521654, '#.0000%'),
    
    -- Formatando porcentagem com 2 casas decimais e utilizando sessão (;)
    FORMAT(0.123456789, '#.00%;-#.00%'),
    -- Formatando porcentagem com 2 casas decimais e utilizando sessão (;)
    FORMAT(-0.123456789, '#.00%;-#.00%'),
    -- Formatando porcentagem com 2 casas decimais e utilizando sessão (;)
    FORMAT(-0.123456789, '#.00%;(#.00%)')

SELECT

-- Formato de moeda brasileira (manualmente)

FORMAT(123456789.9, 'R$ ###,###,###,###.00'),

-- Utilizando sessão (;) para formatar valores positivos e negativos

FORMAT(123456789.9, 'R$ ###,###,###,###.00;-R$ ###,###,###,###.00'),

-- Utilizando sessão (;) para formatar valores positivos e negativos

FORMAT(-123456789.9, 'R$ ###,###,###,###.00;-R$ ###,###,###,###.00'),

-- Utilizando sessão (;) para formatar valores positivos e negativos

FORMAT(-123456789.9, 'R$ ###,###,###,###.00;(R$ ###,###,###,###.00)'),

-- Formatando porcentagem com 2 casas decimais

FORMAT(0.9975, '#.00%'),

-- Formatando porcentagem com 4 casas decimais

FORMAT(0.997521654, '#.0000%'),

-- Formatando porcentagem com 4 casas decimais

FORMAT(123456789.997521654, '#.0000%'),

-- Formatando porcentagem com 2 casas decimais e utilizando sessão (;)

FORMAT(0.123456789, '#.00%;-#.00%'),

-- Formatando porcentagem com 2 casas decimais e utilizando sessão (;)

FORMAT(-0.123456789, '#.00%;-#.00%'),

-- Formatando porcentagem com 2 casas decimais e utilizando sessão (;)

FORMAT(-0.123456789, '#.00%;(#.00%)')

Result:

Formatting dates

Predefined Formatting

Most common formats

SET LANGUAGE 'English'

SELECT
    FORMAT(GETDATE(), 'd'), -- Padrão de data abreviada.
    FORMAT(GETDATE(), 'D'), -- Padrão de data completa.

    FORMAT(GETDATE(), 'R'), -- Padrão RFC1123

    FORMAT(GETDATE(), 't'), -- Padrão de hora abreviada.
    FORMAT(GETDATE(), 'T') -- Padrão de hora completa.
    

SET LANGUAGE 'Brazilian'

SELECT
    FORMAT(GETDATE(), 'd'), -- Padrão de data abreviada.
    FORMAT(GETDATE(), 'D'), -- Padrão de data completa.

    FORMAT(GETDATE(), 'R'), -- Padrão RFC1123

    FORMAT(GETDATE(), 't'), -- Padrão de hora abreviada.
    FORMAT(GETDATE(), 'T') -- Padrão de hora completa.

SET LANGUAGE 'English'

SELECT

FORMAT(GETDATE(), 'd'), -- Padrão de data abreviada.

FORMAT(GETDATE(), 'D'), -- Padrão de data completa.

FORMAT(GETDATE(), 'R'), -- Padrão RFC1123

FORMAT(GETDATE(), 't'), -- Padrão de hora abreviada.

FORMAT(GETDATE(), 'T') -- Padrão de hora completa.

SET LANGUAGE 'Brazilian'

SELECT

FORMAT(GETDATE(), 'd'), -- Padrão de data abreviada.

FORMAT(GETDATE(), 'D'), -- Padrão de data completa.

FORMAT(GETDATE(), 'R'), -- Padrão RFC1123

FORMAT(GETDATE(), 't'), -- Padrão de hora abreviada.

FORMAT(GETDATE(), 'T') -- Padrão de hora completa.

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.

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

"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

SELECT
    -- Formato de data típico do Brasil
    FORMAT(GETDATE(), 'dd/MM/yyyy'),

    -- Formato de data/hora típico dos EUA
    FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss.fff'),

    -- Exibindo a data por extenso
    FORMAT(GETDATE(), 'dddd, dd \d\e MMMM \d\e yyyy'),

    -- Exibindo a data por extenso (forçando o idioma pra PT-BR)
    FORMAT(GETDATE(), 'dddd, dd \d\e MMMM \d\e yyyy', 'pt-br'),

    -- Exibindo a data/hora, mas zerando os minutos e segundos
    FORMAT(GETDATE(), 'dd/MM/yyyy HH:00:00', 'pt-br')

SELECT

-- Formato de data típico do Brasil

FORMAT(GETDATE(), 'dd/MM/yyyy'),

-- Formato de data/hora típico dos EUA

FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss.fff'),

-- Exibindo a data por extenso

FORMAT(GETDATE(), 'dddd, dd \d\e MMMM \d\e yyyy'),

-- Exibindo a data por extenso (forçando o idioma pra PT-BR)

FORMAT(GETDATE(), 'dddd, dd \d\e MMMM \d\e yyyy', 'pt-br'),

-- Exibindo a data/hora, mas zerando os minutos e segundos

FORMAT(GETDATE(), 'dd/MM/yyyy HH:00:00', 'pt-br')

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

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

using System.Data.SqlTypes;
using System.Globalization;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncFormata_Datetime(SqlDateTime Dt_Referencia, SqlString Ds_Mascara)
    {
        return Dt_Referencia.IsNull ? SqlString.Null : Dt_Referencia.Value.ToString(Ds_Mascara.Value, new CultureInfo("pt-BR"));
    }
};

using System.Data.SqlTypes;

using System.Globalization;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncFormata_Datetime(SqlDateTime Dt_Referencia, SqlString Ds_Mascara)

{

return Dt_Referencia.IsNull ? SqlString.Null : Dt_Referencia.Value.ToString(Ds_Mascara.Value, new CultureInfo("pt-BR"));

}

};

How to format numbers with CLR

Examples with standard formats

Custom Format Examples

FncFormata_Numero source code

using System.Data.SqlTypes;
using System.Globalization;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncFormata_Numero(SqlDouble Ds_String, SqlString Ds_Formato)
    {

        if (Ds_String.IsNull)
            return SqlString.Null;

        return Ds_Formato.IsNull ? Ds_String.Value.ToString(CultureInfo.InvariantCulture) : Ds_String.Value.ToString(Ds_Formato.Value, CultureInfo.CreateSpecificCulture("pt-br"));

    }
}

using System.Data.SqlTypes;

using System.Globalization;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncFormata_Numero(SqlDouble Ds_String, SqlString Ds_Formato)

{

if (Ds_String.IsNull)

return SqlString.Null;

return Ds_Formato.IsNull ? Ds_String.Value.ToString(CultureInfo.InvariantCulture) : Ds_String.Value.ToString(Ds_Formato.Value, CultureInfo.CreateSpecificCulture("pt-br"));

}

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

Piero Christofer said:

4 July 2019 to 14: 42

Great post!

It helped me optimize hundreds of queries in which I was doing three conversions per record to bring in currency format.

Congratulations!

- Dirceu Resende said:
  
  19 from 2019 from September to 00: 39
  
  Thank you, Piero.
  
  I hope it was helpful to you 🙂
  Hug
  
Christian Gonçalves said:

17 October 2017 to 15: 03

Show!
Just a doubt, in situations where the date comes as varchar, should I use the converter anyway?

SET LANGUAGE 'Brazilian'
Decare
@Date AS VARCHAR (8);
SET
@Data = '20171017'

SELECT FORMAT (CAST (@Data AS DATETIME), 'D')

- Dirceu Resende said:
  
  18 October 2017 to 07: 52
  
  Christian,
  If it is interesting to change the formatting of the next date in string format (varchar), you can use the FORMAT function, yes.

Other Languages

Subscribe to a blog by email

Blog Views

Categories

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Recent Posts

Other Languages

Are you looking for something?

Subscribe to Blog by Email

Total Views

Courses and training

Microsoft MVP

Acknowledgments and Certificates

Categories

Archives

Recent Posts

SQL Server 2012 - Using the FORMAT function to apply masks and formatting to numbers and dates

Formatting Numeric Data

Most used formats

Other formats

Filling a number with leading zero

Formatting a Number for Brazilian Currency

Formatting dates

Most common formats

Full table:

Full table:

Formatting Numbers and Dates Before SQL Server 2012

How to format dates with CLR

FncFormata_Datetime Source Code

How to format numbers with CLR

FncFormata_Numero source code

You may also like ...

Comments

Leave a Comment Cancel reply

Formatting Numeric Data

Most used formats

Other formats

Filling a number with leading zero

Formatting a Number for Brazilian Currency

Formatting dates

Most common formats

Full table:

Full table:

Formatting Numbers and Dates Before SQL Server 2012

How to format dates with CLR

FncFormata_Datetime Source Code

How to format numbers with CLR

FncFormata_Numero source code

You may also like ...

How to find out the date of last access to a table or view and execution of a procedure in SQL Server

Free SQL Server 2016 MCSA Certification Tests (Beta)

SQL Server - How to export the source code of all Stored Procedures, Functions, Views and Triggers from a database to .sql files

Comments

Leave a Comment Cancel reply