Último dia do mês - SQL Server ( TSQL )



Atualização 04/11/2016

À partir do SQL Server 2012 foi introduzida a função nativa 

EOMONTH ( start_date [, month_to_add ] )
Mais informações podem ser obtidas na página https://msdn.microsoft.com/pt-br/library/hh213020.aspx

Ainda assim o dissertado abaixo ainda possui grande valor no entendimento da manipulação de datas com TSQL, além de continuar válido para as versões inferiores à 2012.

Direto ao assunto.
Pra quem quer direto o código:
declare @data datetime
set @data = CONVERT(char(8),'20100203',1
12)
select dateadd(d,-1,dateadd(m,1,CONVERT(datetime,CONVERT(char(6),@data,112) + '01',112)))
Motivação
Vamos supor que recentemente o analista senior da sua empresa descobriu uma incoerência em alguns relatórios emitidos pelo sistema de ponto de venda. Ele suspeita que o erro seja específico e esteja relacionado somente as vendas feitas no último dia do mês.
Para confirmar a sua suspeita ele solicita a você que faça um select na tebela de vendas retornando somente as vendas que ocorreram no último dia de cada mês do ano de 2010. Você executa um sp_helptext na tabela de vendas e descobre que a informção que corresponde a data da venda está no campo dt_venda. A tabela de vendas tem a seguinte estrutura (muito simplificada só para fins didáticos):



Como retornar somente as vendas que ocorreram no último dia de cada mês do ano de 2010.
Para selecionar as vendas ocorridas somente no ano de 2010 você sabe que basta fazer uma consulta simples como essa:
select * from venda where year(dt_venda) = 2010

Dando uma olhadinha no calendário você descobre que 2010 é bisexto, logo fevereiro termina no dia 28. E o último dia dos demais meses você já sabe de cabeça então seu problema está resolvido. Você monta a seguinte consulta:
set dateformat ymd
select * from venda where dt_venda in (‘2010-01-31’,’2010-02-28’,’2010-03-31’,’2010-04-30’,’2010-05-31’,’2010-06-30’,’2010-07-31’,’2010-08-31’,’2010-09-30’,’2010-10-31’,’2010-11-30’,’2010-12-31’)
*tá vendo? já errei!

Resolve seu problema rapidinho e vai pra casa jogar Wii! No dia seguinte pela parte da tarde o mesmo analista entra em contato com você e diz que o problema aparenta ser maior do que parecia e que vai precisar do resultado da mesma consulta para os todos os anos desde 1955.

O bizuleu.

Você sabe que pode simplesmente seguir a mesma abordagem da vez anterior só que dessa vez você vai ter que escrever 55 * 12 = 660 meses dentro da sua clausula in. O que não é tão trabalhoso assim. Vamos-lá: Agora você pode pegar a consulta anterior e utilizando um bloco de notas fazer substituições de texto (2010 por cada ano) 55 vezes. Feito isso é só ir atrás de dos anos bisextos e fazer os ajustes. Hehe! Pensando bem... Não tem uma maneira melhor não?

A nossa abordagem.

A nossa abordagem consiste em retirar para cada linha o último dia do mês
do próprio campo dt_venda, utilizando para isso duas funções nativas do SQL SERVER. São elas:
DATEADD (datepart , number, date )
e
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
DATEADD
A função DATEADD adiciona a data definida no parâmetro date, o número de datepart definido por number. Tá confuso? Vou tentar explicar melhor.

O parametro obrigatório datepart específica a “unidade” de data que você deseja adicionar. As unidades válidas constam da tabela abaixo.
Datepart
Abrevições
year
yy, yyyy
quarter
qq, q
month
mm, m

dayofyear
dy, y

day
dd, d
week
wk, ww
weekday
dw, w
hour
hh
minute
mi, n

second
ss, s
millisecond
ms
O parametro obrigatório number especifica a quantidade de dateparts que você deseja adicionar.
Finalmente o parâmetro date deve ser a data ao qual você deseja adicionar number X dateparts.

Por exemplo a consulta:
select dateadd(day,22,’2010-01-01’)
Significa: selecione a data correspondente a primeiro de janeiro de 2010 adicionado de vinte e dois dias 

datepart
Detalhado: selecione a data correspondente a
[primeiro de janeiro de 2010]---> date
adicionado de [vinte e dois]------------> number
[dias]----------------------------------------> datepart
o resultado seria vinte e dois de janeiro de 2010 ou ‘2010-01-22’

A consulta select dateadd(month,3,’2009-09-23’) pode ser lida como: selecione a data correspondente a vinte e três de setembro de 2009 passados tres meses. E o seu resultado será ‘2009-12-23’.


Acho que já deu para entender o espírito? Vamos a nossa próxima função.
CONVERT
A função CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) converte uma expressão de um tipo de dados para outro.

O primeiro argumento: data_type é o tipo que você quer obter como retorno. É um argumento obrigatório porém a parte o tamanho do tipo definido por length é opcional. Length será bastante importante para nós!

O segundo argumento: expression representa o seu alvo, ou seja, a variável, coluna ou expressão que você deseja eu seja convertida do seu tipo original para o tipo que foi especificado no primeiro argumento.

O terceiro argumento: style especifica como a conversão deve ser feita em caso de d
atas. Este parâmetro deve ser um inteiro conforme a tabela abaixo.
Sem século (aa) (1)
Com século (aaaa)
Padrão
Entrada/Saída (3)
-
0 ou 100 (1, 2)
Padrão
mês dd aaaa hh:miAM (ou PM)
1
101
EUA
mm/dd/aaaa
2
102
ANSI
aa.mm.dd
3
103
Britânico/francês
dd/mm/aaaa

4
104
Alemão
dd.mm.aa
5
105
Italiano
dd-mm-aa
6
106 (1)
-
dd mês aa
7
107 (1)
-
Mês dd, aa

8
108
-
hh:mi:ss
-
9 ou 109 (1, 2)
Padrão + milissegundos
mês dd aaaa hh:mi:ss:mmmAM (ou PM)
10
110
EUA
mm-dd-aa
11
111
JAPÃO
aa/mm/dd
12
112
ISO
aammdd
aaaammdd
-
13 ou 113 (1, 2)
Padrão Europa + milissegundos
dd mês aaaa hh:mi:ss:mmm (24h)
14
114
-
hh:mi:ss:mmm(24h)
-
20 ou 120 (2)
ODBC canônico
aaaa-mm-dd hh:mi:ss(24h)

-
21 ou 121 (2)
ODBC canônico (com milissegundos)
aaaa-mm-dd hh:mi:ss.mmm(24h)
-
126 (4)
ISO8601
aaaa-mm-ddThh:mi:ss.mmm (sem espaços)
-
127(6, 7)
ISO8601 com fuso horário Z.
aaaa-mm-ddThh:mi:ss.mmmZ
(sem espaços)

-
130 (1, 2)
Islâmico (5)
dd mmm aaaa hh:mi:ss:mmmAM
-
131 (2)
Islâmico (5)
dd/mm/aa hh:mi:ss:mmmAM
Por exemplo a consulta:
select convert(char(8),getdate(),112)

se executada agora irá retornar '20101206' pois estou pedindo que seja selecionada a data do momento convertida para char(8) no formato 112 que pela tabela acima é aaaammdd (ano com 4 digitos, mês com 2 dígitos e dia com 2 dígitos).

A função convert atua na mão e na contra-mão! Estou querendo dizer que assim como ela é capaz de converter um tipo datetime para char ela também é capaz de converter um tipo char para datetime utilizando a mesma tabela de formato.

Ex:
select convert(datetime,'20100112',112 ) -- aaaammdd
select convert(datetime,'2010-01-12',121) -- aaaa-mm-dd
select convert(datetime,'12/01/2010', 103) -- dd/mm/aaaa

Todos os 3 selects tem como retorno o valor do tipo detetime para a data 12 de janeiro de 2010.
Já deu pra perceber que a função convert nos dá completo controle sobre como converter datetime em varchar/char e vice-versa.

Um outro aspecto peculiar e muito importante para nós é um comportamento peculiar de como a conversão é feita. Observe que quando estipulamos o tipo de conversão no parâmetro data_type também estipulamos o tamnaho dele. O que acontece quando estipulamos um valor menor do que o total necessário para conversão? A resposta para essa pergunta é que a funcão trunca (suprime) a parte direita do retorno trazendo um varchar ou char exatamente do tamanho estipulado no length.

Já estamos utilizando esse comportamento. Quando definimos o data_type como char(8) nos nossos exemplos anteriores estamos instruindo a função a ignorar os minutos e segundos e milisegundos da data. Desse mesmo jeito se definíssemos um data_type como sendo char(6) e o style como 112 ignoraríamos a parte dia (dd) do retorno ficando apenas com aaaamm.

Ex:
select convert(char(6),getdate(),112) -- retorna o char(6) 201012
Juntando tudo.

Juntando tudo para que possamos obter o último dia de determinado mes com total segurança.
declare @data datetime
set @data = CONVERT(char(8),'20100203',112)
select dateadd(d,-1,dateadd(m,1,CONVERT(datetime,CONVERT(char(6),@data,112) + '01',112)))

A lógica é a seguinte:
  1. CONVERT(char(6),@data,112) -- convertemos a data para char(6) no estilo 112 para termos como retorno só a parte aaaamm - '201002'
  2. '201002' + '01' -- O resultado da conversão anterior é concatenada com '01' para obtermos o 1 dia do mês então ficamos com um varchar(8) - '20100201'
  3. CONVERT(datetime,'20100201',112) -- fazemos uma outra conversão do resultado para data afim de podermos utilizar as função dateadd sem passar pelas pegadinhas das conversões implícitas. - '20100201 00:00:00.000'
  4. dateadd(m,1,'20100201 00:00:00.000') -- adicionamos um mês ao resultado anterior, então teremos o primeiro dia do mês seguinte. - '20100301 00:00:00.000'
  5. dateadd(d,-1,'20100301 00:00:00.000') -- finalmente retiramos um dia do mês seguinte e então teremos o último dia do mês da data inicial ao qual nos propomos - '2010-02-28 00:00:00.000'

Comentários

  1. Muito boa a explicação! Muito bom mesmo! Parabéns pelo trabalho!

    ResponderExcluir

Postar um comentário

Obrigado pelo seu comentário!

Postagens mais visitadas deste blog

Curtir publicações automaticamente no Facebook. Por que isso não é uma boa idéia?