SQL Server – Utilizando a função FORMAT para aplicar máscaras e formatações em números e datas

SQL Server – Utilizando a função FORMAT para aplicar máscaras e formatações em números e datas
Avalie esse post

Olá pessoal,
Tudo bem ?

Neste post de hoje eu gostaria de mostrar pra vocês a função T-SQL FORMAT, disponível desde o SQL Server 2012, e que até hoje pouca gente utiliza no dia a dia para formatação de datas e números.

Quando vou analisar queries, funções e Stored Procedures, vejo que ainda hoje, vários desenvolvedores insistem em utilizar CAST, CONVERT e concatenações para formatar datas e números, mesmo com uma função especificamente para isso. Após ler esse post, espero que você entenda como utilizar essa função e comece a simplificar seus códigos T-SQL com ela.

Quem nunca precisou preencher um número com 0 à esquerda para gerar layout? Formatar uma data? Extrair somente a hora de uma data? São inúmeras situações em que a função FORMAT é útil.

Formatando dados numéricos

Formatação Pré-definida
Uso mais simples, as funções pré-definidas permitem a formatação de valores utilizando máscaras já definidas por padrão no SQL Server, como “C” para modela (currency).

Formatos mais utilizados

Exemplos

Outros formatos

Exemplos

Formatação Personalizada

Preenchendo um número com zero à esquerda

Formatando um número para moeda brasileira

Função utilizada nesse exemplo:

Outros exemplos:

Resultado:

Formatando datas

Formatação Pré-definida

Formatos mais comuns

Resultado:

Tabela completa:

Formato

Descrição

Exemplos

"d"

Padrão de data abreviada.

Mais informações: Especificador de formato abreviado de data ("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"

Padrão de data completa.

Mais informações:Especificador de formato de data completa ("D").

2009-06-15T13:45:30 -> segunda-feira, 15 de junho de 2009 (en-US)

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

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

"f"

Padrão de data/hora completa (hora abreviada).

Mais informações: Especificador de formato de data completa e hora abreviada ("f").

2009-06-15T13:45:30 -> segunda-feira, 15 de junho de 2009 1:45 PM (en-US)

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

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

"F"

Padrão de data/hora completa (hora completa).

Mais informações: Especificador de formato de data completa e hora completa ("F").

2009-06-15T13:45:30 -> segunda-feira, 15 de junho de 2009 1:45:30 PM (en-US)

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

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

"g"

Padrão geral de data/hora (hora abreviada).

Mais informações: Especificador de formato geral de data e hora abreviada ("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"

Padrão geral de data/hora (hora completa).

Mais informações: Especificador de formato geral de data e hora completa ("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"

Padrão de mês/dia.

Mais informações: Especificador de formato de mês ("M", "m").

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

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

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

"O", "o"

Padrão de data/hora de viagem de ida e volta.

Mais informações: Especificador de formato de viagem de ida e volta ("O", "o").

DateTime valores:

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 valores:

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

"R", "r"

Padrão RFC1123

Mais informações: Especificador de formato RFC1123 ("R", "r").

2009-06-15T13:45:30 -> segunda-feira, 15 de junho de 2009 20:45:30 GMT

"s"

Padrão de data/hora classificável.

Mais informações: Especificador de formato classificável ("s").

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

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

"t"

Padrão de hora abreviada.

Mais informações: Especificador de formato de hora abreviada ("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 م (ar-EG)

"T"

Padrão de hora completa.

Mais informações: Especificador de formato de hora completa ("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"

Padrão classificável universal de data/hora.

Mais informações: Especificador de formato de padrão classificável universal ("u").

Com uma DateTime valor: 2009-06-2009-06-15-> 15T13:45:30 13:45:30Z

Com uma DateTimeOffset valor: 2009-06-2009-06-15-> 15T13:45:30 20:45:30Z

"U"

Padrão universal de data/hora completa.

Mais informações: Especificador de formato de padrão universal completo ("U").

2009-06-15T13:45:30 -> segunda-feira, 15 de junho de 2009 8:45:30 PM (en-US)

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

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

"Y", "y"

Padrão ano mês.

Mais informações: Especificador de formato de ano mês ("Y").

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

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

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

Qualquer outro caractere único

Especificador desconhecido.

Gera uma FormatException de tempo de execução.

Formatação personalizada

Resultado:

Tabela completa:

Formato

Descrição

Exemplos

"d"

O dia do mês, de 1 a 31.

Obter mais informações: o especificador de formato personalizado "d".

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

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

"dd"

O dia do mês, de 01 a 31.

Obter mais informações: o especificador de formato personalizado "dd".

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

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

"ddd"

O nome abreviado do dia da semana.

Obter mais informações: o especificador de formato personalizado "ddd".

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

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

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

"dddd"

O nome completo do dia da semana.

Obter mais informações: o especificador de formato personalizado "dddd".

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

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

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

"f"

Os décimos de segundo em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "f".

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

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

"ff"

Os centésimos de segundo em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "ff".

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

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

"fff"

Os milissegundos em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "fff".

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

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

"ffff"

Os décimos de milésimos de segundo em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "ffff".

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

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

"fffff"

Os centésimos de milésimos de segundo em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "fffff".

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

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

"ffffff"

Os milionésimos de segundo em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "ffffff".

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

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

"fffffff"

Os décimos de milionésimos de segundo em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "fffffff".

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

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

"F"

Se diferente de zero, os décimos de segundo em um valor de data e hora.

Obter mais informações: especificador de formato personalizado "F".

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

2009-06-15T13:45:30.0500000 -> (sem saída)

"FF"

Se diferente de zero, os centésimos de segundo em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "FF".

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

2009-06-15T13:45:30.0050000 -> (sem saída)

"FFF"

Se diferente de zero, os milissegundos em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "FFF".

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

2009-06-15T13:45:30.0005000 -> (sem saída)

"FFFF"

Se diferente de zero, os décimos de milésimos de segundo em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "FFFF".

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

2009-06-15T13:45:30.0000500 -> (sem saída)

"FFFFF"

Se diferente de zero, os centésimos de milésimos de segundo em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "FFFFF".

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

2009-06-15T13:45:30.0000050 -> (sem saída)

"FFFFFF"

Se diferente de zero, os milionésimos de segundo em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "FFFFFF".

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

2009-06-15T13:45:30.0000005 -> (sem saída)

"FFFFFFF"

Se diferente de zero, os décimos de milionésimos de segundo em um valor de data e hora.

Obter mais informações: o especificador de formato personalizado "FFFFFFF".

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

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

"g", "gg"

O período ou era.

Obter mais informações: "g" ou "gg" especificador de formato personalizado.

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

"h"

A hora, usando um relógio de 12 horas de 1 a 12.

Obter mais informações: o especificador de formato personalizado "h".

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

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

"hh"

A hora, usando um relógio de 12 horas de 01 a 12.

Obter mais informações: o especificador de formato personalizado "hh".

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

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

"H"

A hora, usando um relógio de 24 horas de 0 a 23.

Obter mais informações: especificador de formato personalizado "H".

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

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

"HH"

A hora, usando um relógio de 24 horas de 00 a 23.

Obter mais informações: o especificador de formato personalizado "HH".

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

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

"K"

Informações de fuso horário.

Obter mais informações: o especificador de formato personalizado "K".

Com DateTime valores:

2009-06-15T13:45:30, que tipo não especificado ->

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

2009-06-15T13:45:30, tipo Local->-07:00 (depende de configurações do computador local)

Com DateTimeOffset valores:

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

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

"m"

O minuto, de 0 a 59.

Obter mais informações: o especificador de formato personalizado "m".

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

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

"mm"

O minuto, de 00 a 59.

Obter mais informações: o especificador de formato personalizado "mm".

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

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

“M”

O mês, de 1 a 12.

Obter mais informações: o especificador de formato personalizado "M".

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

"MM"

O mês, de 01 a 12.

Obter mais informações: o especificador de formato personalizado "MM".

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

"MMM"

O nome abreviado do mês.

Obter mais informações: o especificador de formato personalizado "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"

O nome completo do mês.

Obter mais informações: o especificador de formato personalizado "MMMM".

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

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

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

"s"

O segundo, de 0 a 59.

Obter mais informações: o especificador de formato personalizado "s".

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

"ss"

O segundo, de 00 a 59.

Obter mais informações: o especificador de formato personalizado "ss".

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

"t"

O primeiro caractere do designador AM/PM.

Obter mais informações: o especificador de formato personalizado "t".

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

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

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

"tt"

O designador AM/PM.

Obter mais informações: o especificador de formato personalizado "tt".

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

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

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

"y"

O ano, de 0 a 99.

Obter mais informações: o especificador de formato personalizado "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"

O ano, de 00 a 99.

Obter mais informações: o especificador de formato personalizado "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"

O ano, com um mínimo de três dígitos.

Obter mais informações: o especificador de formato personalizado "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"

O ano como um número de quatro dígitos.

Obter mais informações: o especificador de formato personalizado "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"

O ano como um número de cinco dígitos.

Obter mais informações: o especificador de formato personalizado "yyyyy".

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

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

"z"

Diferença de horas UTC, sem zeros à esquerda.

Obter mais informações: o especificador de formato personalizado "z".

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

"zz"

Diferença de horas UTC, com um zero à esquerda de um valor de um único dígito.

Obter mais informações: o especificador de formato personalizado "zz".

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

"zzz"

Horas e minutos deslocamento do UTC.

Obter mais informações: o especificador de formato personalizado "zzz".

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

":"

O separador de hora.

Obter mais informações: o ":" especificador de formato personalizado.

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

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

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

"/"

O separador de data.

Mais informações: o especificador de formato personalizado "/".

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

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

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

"seqüência"

'seqüência'

Delimitador de cadeia de caracteres literal.

Obter mais informações: Literais de caracteres.

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

2009-06-15T13:45:30 ('arr:' h:m t) -> arr: 1:45 P

%

Define o caractere seguinte como um especificador de formato personalizado.

Obter mais informações:usando especificadores de formato personalizado simples.

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

\

O caractere de escape.

Obter mais informações: Literais de caracteres e usando o caractere de Escape.

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

Qualquer outro caractere

O caractere é copiado para a cadeia de caracteres de resultado inalterada.

Obter mais informações: Literais de caracteres.

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

Formatando números e datas antes do SQL Server 2012

Agora que vocês viram como é simples e rápido formatar números e datas utilizando a função nativa FORMAT, fica até difícil querer voltar a formatar esses tipos de dados manualmente, correto?

Infelizmente, a função FORMAT foi introduzida no SQL Server 2012, ou seja, nas versões 2005 e 2008, você ainda terá que utilizar as formas tradicionais (custosas e trabalhosas) de formatação de datas e números. Ou não.

Para quem tem um database project na sua instância (também conhecido como SQLCLR), você pode facilmente implementar 2 funções muito similares (praticamente iguais) a função FORMAT, permitindo que você possa formatar dados facilmente utilizando essas funções, mesmo nas versões 2005 e 2008 do SQL Server.

Caso você não conheça o SQLCLR, ou não saiba como criar seu primeiro projeto, dê uma lida no post Introdução ao SQL CLR (Common Language Runtime) no SQL Server.

Como formatar datas com o CLR

Vejam como a utilização é praticamente igual ao da função FORMAT (Não implementei o terceiro parâmetro – culture):

Reparem que, tanto os formatos pré-definidos quantos os formatos personalizados permanecem inalterados utilizando a função do CLR. Isso acontece porque a função FORMAT utiliza internamente, a mesma função do C# que utilizei nessas funções do SQLCLR.

Código-fonte da fncFormata_Datetime

Como formatar números com o CLR

Exemplos com formatos padrão

Exemplos com formatos personalizados

Código-fonte da fncFormata_Numero

É isso aí, pessoal!
Espero que vocês passem a utilizar mais a função FORMAT no dia a dia de vocês (quando necessário) e caso estejam utilizando as versões 2005 ou 2008 do SQL Server, saibam que é possível simular o comportamento dessa função utilizando o SQLCLR.

Um grande abraço e até mais.

Referências:
FORMAT (Transact-SQL)
Formatando tipos no .NET Framework
Cadeias de caracteres de formato numérico padrão
Cadeias de caracteres de formato numérico personalizado
Cadeias de caracteres de formato de data e hora padrão
Cadeias de caracteres de formato de data e hora personalizado

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

2 Comments

Deixe uma resposta