Pesquisar este blog

domingo, 28 de novembro de 2010

Como tratar valores DATA (datetime) no SQL SERVER

Este artigo se aplica a qualquer versão do Microsoft SQL Server a partir da versão 2000.
TAGS: #DATEADD, #DATEPART, #GETDATE, #BISSEXTO, #SQL, #SQLSERVER

O SQL possui inúmeras ferramentas para o tratamento de datas. Aqui vou apresentar algumas dicas que considero as mais relevantes, que poderão ajudá-lo no desenvolvimento de suas querys.

CALCULAR DATA INICIAL E FINAL A PARTIR DO MÊS/ANO

Você está programando um código que utiliza uma procedure que já existe no banco de dados. O parâmetro para o cálculo que você possui é o mês e o ano, porém esta procedure recebe o parametro @data_inicial e @data_final. Como você faz para chamar este procedimento, se você só tem o mês e o ano?

Bom, o macete que vou apresentar aqui é muito simples. Vamos utilizar a função DATEADD. A função DATEADD permite adicionar ou subtrair valores específicos de uma data. Então, para começar, montamos a data inicial:

select @dt_inicial = rtrim(@ano) + '-' + right('00' + rtrim(@mes),2) + '-01'

Nesta query, estamos concatenando o ano, o mês e o dia 01 fixo para o @mes e @ano que temos como parâmetro. O código right(‘00’ + rtrim(@mes,2)  serve para sempre retornar o mês com dois dígitos (01, 05, 11, 12). O formato da data que devemos montar é YYYY-MM-DD.

Após calcularmos a data incial, vamos utilizar a função DATEADD duas vezes. Vamos somar um mês e subtrair um dia, nesta sequencia, para descobrirmos o último dia do mês:

select @dt_final = dateadd(day, -1, dateadd(month, 1, @dt_inicial))

A query dateadd(month, 1, @dt_inicial) retorna a data inicial com 1 mês a mais, e a função dateadd(day, –1, … ) pega este valor e subtrai um dia, desta forma teremos o último dia do @mes e @ano.

Segue abaixo o código completo para que possa copiar:


Code Snippet
  1. --
  2.  
  3. -- @mes = 3, @ano = 2010
  4. declare @mes smallint
  5. declare @ano smallint
  6. declare @dt_inicial smalldatetime
  7. declare @dt_final smalldatetime
  8.  
  9. select @mes = 2, @ano = 2012
  10.  
  11. select @dt_inicial = rtrim(@ano) + '-'
  12.                    + right('00' + rtrim(@mes),2) + '-01'
  13.  
  14. select @dt_final = dateadd(day, -1, dateadd(month, 1, @dt_inicial))
  15.  
  16. select @dt_inicial as 'Data inicial', @dt_final as 'Data final'
  17.  
  18.                     
  19. --


 image

 


CÁLCULO DE ANO BISSEXTO

Já vi programadores fazerem cada código bizarro para descobrir se o ano é bissexto. Primeiro, que eu realmente não vejo utilidade em saber se o ano é bissexto ou não. Ah, é para definir uma variável data com o último dia do mês, mas não sabe se termina com 28, 29, 30 ou 31 dias? Esqueça isso, utilize a dica que mostrei acima. Mas de qualquer forma, o truque para calcular se um ano é bissexto é que o resultado da divisão do ano por 4 (quatro) não pode ser maior que zero. No SQL, para se descobrir o resto de uma divisão, utilizamos o operador MOD (%). Veja a query abaixo:

image

Se o cálculo @ANO % 4 = 0, significa que o ano é bissexto, assim como no exemplo acima (ano 2012).

UTILIZE A FUNÇÃO DATEPART e CONVERT PARA OBTER QUALQUER VALOR DE UMA DATA ESPECÍFICA

Existem casos em que precisamos extrair o ano de uma data específica, ou hora e minutos, e assim por diante. Vou apresentar alguns exemplos úteis de utilização da função DATEPART e CONVERT.

1. Pegando valores individuais de uma data (dia, mes, ano, hora, minutos e segundos) –


Code Snippet
  1. --
  2.  
  3. declare @dia smallint, @mes smallint, @ano smallint
  4.       , @hr  smallint, @min smallint, @seg smallint
  5.  
  6. select @dia = datepart(day, getdate())
  7.      , @mes = datepart(month, getdate())
  8.      , @ano = datepart(year, getdate())
  9.      , @hr  = datepart(hour, getdate())
  10.      , @min = datepart(minute, getdate())
  11.      , @seg = datepart(second, getdate())
  12.  
  13. select getdate() as 'Getdate() original'
  14.      , @dia as 'Dia'
  15.      , @mes as 'Mes'
  16.      , @ano as 'Ano'
  17.      , @hr  as 'Hora'
  18.      , @min as 'Minuto'
  19.      , @seg as 'Segundo'
  20.  
  21.                     
  22. --



* A função GETDATE() retorna a data e hora atual do servidor SQL.

image 

2. Os tipos de CONVERT mais comuns com datas (#SQL = formato de data padrão do SQL YYYY-MM-DD).


Code Snippet
  1. --
  2.  
  3. select getdate() as 'Getdate() original'
  4.      , convert(char(10), getdate(), 120) '#SQL'
  5.      , convert(char(10), getdate(), 103) 'PT-BR'
  6.      , convert(char(10), getdate(), 101) 'US-EN'
  7.      , convert(char(8), getdate(), 14) 'TEMPO-FULL'
  8.      , convert(char(5), getdate(), 14) 'TEMPO-SHORT'
  9.  
  10. --



image 

3D GEARS GEAR7

9 comentários:

  1. Gostei muito! Sempre tive vontade de saber como se calculava isso..Parabéns pelo blog!

    ResponderExcluir
  2. Muito obrigado. Ajudou-me a resolver um problema para fazer uma rotina de backup com 1 arquivo para cada mês.
    Deus te abençõe.
    Um abraço.

    Fernando de A. Cadalso

    ResponderExcluir
  3. O seu calculo para ano bissexto está completamente ERRADO. O ano 1900 é divisivel por 4 e não é bissexto.
    Por definição, o ano bissexto é divisivel por 4. Mas se ele for divisivel por 100, sem o ser por 400, ele não é bissexto.
    declare @ano int
    declare @Ebissexto bit
    select @ano=1900
    if ((@ano % 4 = 0) And (Not(@ano % 100 = 0))) Or (@ano % 400 = 0)
    select @Ebissexto = 1
    else
    select @Ebissexto = 0
    select @Ebissexto

    ResponderExcluir
    Respostas
    1. Caro anônimo, o COMPLETAMENTE errado é só uma questão de interpretação, pois não acredito que estarei aqui no ano de 2100, muito menos tirarei relatórios do ano de 1900.

      mas concordo em partes com você, está errado. A regra é, se o ano terminar com 2 zeros (00), é necessário fazer a conta do % 400.

      Obrigado pela dica.

      Excluir
  4. Este comentário foi removido pelo autor.

    ResponderExcluir
  5. Parabéns pelo post, muito bom.
    Tenho uma dúvida e queria saber se poderia me ajudar, preciso extrair o dia de uma data, até aí blz, uso a função Day, mas ela me traz com 1 dígito quando é menor que 10, preciso que me traga com dois dígitos, tipo 07, 09....
    Help me?

    ResponderExcluir
    Respostas
    1. são coisas diferentes, mas vamos lá!!!

      se você quer o dia com zero à esquerda, basta:

      select right('00' + rtrim(day(getdate())), 2)

      prontinho!

      Excluir
    2. Neste artigo aqui, eu explico mais detalhadamente essa questão do zero à esquerda!!!
      http://sqlburger.blogspot.com.br/2010/11/o-truque-do-zero-esquerda.html

      Abraço!

      Excluir