Datas desmistificadas em SQL

[jothaz]

Camaradas,

Neste tópico vamos aprender como manipular Data (DATETIME ou SMALLDATETIME) no SQL Server.

Não é um texto dogmático nem com pretensão de esgotar o assunto. As dicas e exemplos postados aqui representam o meu aprendizado diário. Funcionam! Porém não são verdades absolutas. :blush:

1. Sempre criar campos data com o formato DATETIME ou SMALLDATETIME. A diferença entre os dois tipos podem ser detalhadas nos Books Online. O exemplo a seguir mostra de forma simplificada como cada tipo atua:

   --
   --Retorna Data do sistema completa: AAAA-MM-DD 00:00:00.000
   SELECT CAST(GETDATE() AS DATETIME)
   --
   --Retorna Data do sistema completa (com arredondamento): AAAA-MM-DD 00:00:00
   SELECT CAST(GETDATE() AS SMALLDATETIME)
   --
   --   Exemplos do Books Online
   -- 
   --Retorna: 2000-05-08 12:35
   SELECT CAST('2000-05-08 12:35:29.998' AS SMALLDATETIME)
   SELECT CONVERT(SMALLDATETIME,'2000-05-08 12:35:29.998')
   --
   --Retorna: 2000-05-08 12:36
   SELECT CAST('2000-05-08 12:35:29.999' AS SMALLDATETIME)
   SELECT CONVERT(SMALLDATETIME,'2000-05-08 12:35:29.999')
   --

   OBS: Notem que tanto a função CAST como CONVERT retorna o mesmo resultado. Não existem impedimentos para se gravar uma data em um campo VARCHAR e depois utilizar CAST ou CONVERT para manipulá-lo. Porém como o tipo VARCHAR não impõe CONTRAINT para validar uma data, facilidade nativa dos tipos DATETIME/SMALLDATETIME, pode permitir a entrada de uma data inválida. Assim sendo se a aplicação por algum problema passar uma data invalida o banco de dados gravará a data inválida sem retornar erro algum. É claro que a aplicação realmente dever consistir e formatar a data a ser envia para o banco. Porém definindo a data no formato correto esta consistência será garantida a nível de banco de dados, além da aplicação. No post: http://forum.imasters.com.br/index.php?showtopic=223962 este assunto é tratado com um exemplo real.
2. Criando campos e variáveis do tipo data:

   --Criando variáveis locais do tipo data
       DECLARE @DT_INICIO DATETIME
       DECLARE @DT_INICIO SMALLDATETIME
   
       --Criando campos tipo data em tabela
       CREATE TABLE #TMP (NU_COD INT, DT_INICIO DATETIME)
       CREATE TABLE #TMP (NU_COD INT, DT_INICIO SMALLDATETIME)

3. A forma como o banco de dados tratará/gravará/exibrá o campo data:

   --
   -- Exemplos do Books Online
   SET DATEFORMAT mdy
   DECLARE @datevar datetime
   SET @datevar = '12/31/98'
   SELECT @datevar
   
   SET DATEFORMAT ydm
   SET @datevar = '98/31/12'
   SELECT @datevar
   
   SET DATEFORMAT ymd
   SET @datevar = '98/12/31'
   SELECT @datevar

   No post: http://forum.imasters.com.br/index.php?showtopic=223962 nosso companheiro eriva_br dá uma aula sobre o assunto. Vale uma conferida!
4. Inserindo registros em campos datas

   CREATE TABLE #ATIVIDADES (DESCRICAO_ATIVIDADE VARCHAR(30),DT_INI DATETIME,    DT_FIM DATETIME)
   
   --Inserindo DT_INI no formato DD/MM/AAAA com a hora  
   --e DT_FIM no formato DD/MM/AAAA sem a hora  
   INSERT INTO #ATIVIDADES (DESCRICAO_ATIVIDADE,DT_INI,DT_FIM) 
                   VALUES     ('Requisitos','01/01/2005 00:00:00','01/01/2006') 
   
   --Inserindo DT_INI no formato AAAA/MM/DD sem a hora  
   --e DT_FIM no formato DD/MM/AAAA sem a hora  
   INSERT INTO #ATIVIDADES (DESCRICAO_ATIVIDADE,DT_INI,DT_FIM) 
                   VALUES     ('Modelagem','2006/01/02 ','05/07/2006') 
   
   --Inserindo DT_INI no formato AAAA/DD/MM com a hora  
   --e DT_FIM no formato AAAA/DD/MM com a hora  
   INSERT INTO #ATIVIDADES (DESCRICAO_ATIVIDADE,DT_INI,DT_FIM) 
                   VALUES     ('Implementação','2006/07/06 00:00:00','2007/04/25 00:00:00')

   Como pode ser visto as datas podem ser inseridas de várias formas com hora ou não. Caso a hora não seja informada será usada 00:00:00 como padrão. Aqui onde trabalhamos normalmente enviamos a data para o SQL no formato AAAA/MM/DD pois neste formato grava-se a data em qualquer banco de dados ANSI.
5. Converter, mascarar, recuperar informações campos data: Utilizando a função CONVERT pode-se retornar o conteúdo do campo data das seguintes formas:

   SELECT CONVERT(VARCHAR(12),GETDATE(),101) AS '101',
   CONVERT(VARCHAR(12),GETDATE(),102) AS '102',
   CONVERT(VARCHAR(12),GETDATE(),103) AS '103',
   CONVERT(VARCHAR(12),GETDATE(),104) AS '104',
   CONVERT(VARCHAR(12),GETDATE(),105) AS '105',
   CONVERT(VARCHAR(12),GETDATE(),106) AS '106',
   CONVERT(VARCHAR(12),GETDATE(),107) AS '107',
   CONVERT(VARCHAR(12),GETDATE(),108) AS '108',
   CONVERT(VARCHAR(12),GETDATE(),109) AS '109',
   CONVERT(VARCHAR(12),GETDATE(),110) AS '110',
   CONVERT(VARCHAR(12),GETDATE(),111) AS '111',
   CONVERT(VARCHAR(12),GETDATE(),112) AS '112',
   CONVERT(VARCHAR(12),GETDATE(),113) AS '113',
   CONVERT(VARCHAR(12),GETDATE(),114) AS '114',
   CONVERT(VARCHAR(12),GETDATE(),120) AS '120',
   CONVERT(VARCHAR(12),GETDATE(),121) AS '121'

   A função CONVERT além de devolver a data formatada, ou parte da mesma, permite efetuar a conversão do campo data para outros formatos. Nesta caso pode-se utilizar a funçao CAST para obter-se o mesmo resultado. Para concatenar um texto com uma data veja os exemplos:

   SELECT CONVERT(VARCHAR(10),GETDATE(),103) + ' é a data de hoje'
   SELECT CAST(GETDATE() AS VARCHAR(10)) + ' é a data de hoje'

   Normalmente utilizo o CONVERT e não o CAST, mas cada caso é um caso. Obs: Muita atenção no resultado da conversão de datas para outros formatos. As vezes não ocorre a concatenação e sim uma soma, principalmente na conversões para formato numérico.
6. Manipulando partes de data:

   SELECT     YEAR(GETDATE()) AS ANO,
               MONTH(GETDATE()) AS MES,
               DAY(GETDATE()) AS DIA
   --
   --Utlizando DATEPART que retorna um INTEIRO contendo informações
   --sobre o campo data
   SELECT     DATEPART(YEAR, GETDATE()) AS  ANO,
               DATEPART(MONTH, GETDATE()) AS  MES,
               DATEPART(DAY, GETDATE()) AS  DIA,
   
               DATEPART(DAYOFYEAR, GETDATE()) AS  DIA_ANO,
               DATEPART(WEEK, GETDATE()) AS  SEMANA,
               DATEPART(HOUR, GETDATE()) AS  HORA,
               DATEPART(MINUTE, GETDATE()) AS MINUTO
   --
   --Utlizando DATENAME que retorna uma STRING(caracter) contendo informações
   --sobre o campo data
   SELECT     DATENAME (YEAR, GETDATE()) AS  ANO,
               DATENAME (MONTH, GETDATE()) AS  MES,
               DATENAME (DAY, GETDATE()) AS  DIA,
   
               DATENAME (DAYOFYEAR, GETDATE()) AS  DIA_ANO,
               DATENAME (WEEK, GETDATE()) AS  SEMANA,
               DATENAME (HOUR, GETDATE()) AS  HORA,
               DATENAME (MINUTE, GETDATE()) AS MINUTO

   Mais exemplos e particularidades de cada função pró-atividade e Books Online(procure por Date and Time Functions). :assobiando:
7. Datas e matemática. É possível? :devil:

   --
   --DATEDIFF  retorna um INTEITO como resultado de operações
   --entre dua datas
   DECLARE     @DT_EXEMPLO DATETIME
   SET         @DT_EXEMPLO = '1966/01/01'
   
   SELECT     DATEDIFF(YEAR, @DT_EXEMPLO , GETDATE()) AS ANO,
               DATEDIFF(MONTH, @DT_EXEMPLO , GETDATE()) AS MES,
               DATEDIFF(DAY, @DT_EXEMPLO , GETDATE()) AS DIAS    
   --
   --DATEADD retorna um DATETIME como resultado de adição entre datas
   --
   SELECT     DATEADD(DAY, -10 , GETDATE()) AS DATA_ATUAL_MENOS_10_DIAS,
               DATEADD(DAY, 10 , GETDATE()) AS DATA_ATUAL_MAIS_10_DIAS,
                DATEADD(YEAR, -10 , GETDATE()) AS DATA_ATUAL_MENOS_10_ANOS,
               DATEADD(YEAR, 10 , GETDATE()) AS DATA_ATUAL_MAIS_10_ANOS

   De novo, novamente! Mais exemplos e particularidades de cada função pró-atividade e Books Online(procure por Date and Time Functions). :assobiando:
8. O exemplo a seguir cria uma tabela temporária e executa vários select´s utilizando campos tipo data:

   CREATE TABLE #ATIVIDADES (DESCRICAO_ATIVIDADE VARCHAR(30),DT_INI DATETIME,    DT_FIM DATETIME)
   INSERT INTO #ATIVIDADES (DESCRICAO_ATIVIDADE,DT_INI,DT_FIM) VALUES ('Requisitos','2005/01/01 00:00:00','2006/01/01 00:00:00') 
   INSERT INTO #ATIVIDADES (DESCRICAO_ATIVIDADE,DT_INI,DT_FIM) VALUES ('Modelagem','2006/01/02 00:00:00','2006/07/05 00:00:00') 
   INSERT INTO #ATIVIDADES (DESCRICAO_ATIVIDADE,DT_INI,DT_FIM) VALUES ('Implementação','2006/07/06 00:00:00','2007/04/25 00:00:00') 
   INSERT INTO #ATIVIDADES (DESCRICAO_ATIVIDADE,DT_INI,DT_FIM) VALUES ('Testes','2007/04/26 00:00:00','2007/06/20 00:00:00') 
   INSERT INTO #ATIVIDADES (DESCRICAO_ATIVIDADE,DT_INI,DT_FIM) VALUES ('Homoçogação','2007/06/21 00:00:00','2007/07/28 00:00:00') 
   INSERT INTO #ATIVIDADES (DESCRICAO_ATIVIDADE,DT_INI,DT_FIM) VALUES ('Implantação','2007/07/28 00:00:00','2007/11/25 00:00:00') 
   
   --
   -- Calculando a diferença entre a DT_INI e DT_FIM utilizando função DATADIFF
   --
   PRINT 'DT_FIM - DT_INI:'
   SELECT  CONVERT(VARCHAR(10),DT_INI,103) AS DT_INI,
           CONVERT(VARCHAR(10),DT_FIM,103) AS DT_FIM,
           DATEDIFF(MONTH,DT_INI,DT_FIM) AS DIF_MES,
           DATEDIFF(DAY,DT_INI,DT_FIM) AS DIF_DIAS,
           DATEDIFF(WEEK,DT_INI,DT_FIM) AS DIF_SEMANA,
           DATEDIFF(HOUR,DT_INI,DT_FIM) AS DIF_HORA,
           DATEDIFF(MINUTE,DT_INI,DT_FIM) AS DIF_MINUTOS
   FROM     #ATIVIDADES 
   
   --
   -- Diminui 2 dias e soma 2 dias na DT_INI
   --
   PRINT 'SUBTRAI/SOMA DIAS EM DATA'
   SELECT  CONVERT(VARCHAR(10),DT_INI,103) AS DT_INI,
           CONVERT(VARCHAR(10),DT_FIM,103) AS DT_FIM,
           CONVERT(VARCHAR(10),DATEADD(DAY,-2,DT_FIM),103) AS DOIS_DIAS_ANTES,
           CONVERT(VARCHAR(10),DATEADD(DAY,2,DT_FIM),103) AS DOIS_DIAS_DEPOIS
   FROM     #ATIVIDADES 
   
   --
   --USANDO CASE NAS EXPRESSÕES:
   --
   PRINT 'Data Atual (GETDATE()) - DT_FIM: '
   SELECT  CONVERT(VARCHAR(10),DT_INI,103) AS DT_INI,
           CONVERT(VARCHAR(10),DT_FIM,103) AS DT_FIM,
           DATEDIFF(DAY,DT_INI,DT_FIM) AS 'DT_FIM-DT_INI',
           DATEDIFF(DAY,DT_INI,GETDATE()) AS 'GETDATE()-DT_INI',
           DATEDIFF(DAY,DT_FIM,GETDATE()) AS 'GETDATE()-DT_FIM',
   
           CASE
               WHEN     GETDATE() BETWEEN DT_INI AND DT_FIM  THEN 'Iniciado'
               WHEN     (DATEDIFF(DAY,DT_FIM,GETDATE())) < 1 THEN 'A iniciar'
               ELSE     'Finalizado'
           END AS DE_SITUACAO,
   
           CASE
               WHEN (DATEDIFF(DAY,DT_FIM,GETDATE())) > 0 THEN '100 %'
               ELSE 
                   CASE
                       WHEN      CONVERT(VARCHAR(15),100 - ((100 * (DATEDIFF(DAY,DT_FIM,GETDATE()) * -1) ) / 
                               DATEDIFF(DAY,DT_INI,DT_FIM))) < 0 THEN '0 %'
                       ELSE     CONVERT(VARCHAR(15),100 - ((100 * (DATEDIFF(DAY,DT_FIM,GETDATE()) * -1) ) / 
                               DATEDIFF(DAY,DT_INI,DT_FIM))) + '%'
                   END
          END AS PERC_REAL
   
   FROM #ATIVIDADES DROP TABLE #ATIVIDADES

Considerações:

1. Esta é a primeira versão prometo efetuar as alterações e correções necessárias bem como incluir mais exemplos e comentários.
2. Peço a colaboração de todos para melhorarmos este post.

AUTOR: "Jothaz" - o material deste post foi compilado a partir dos Books Online, material do fórum imasters, de outros fóruns, da internet e das surras que eu já levei por causa das danadas das datas :P . Se por acaso alguém achar que o material, ou parte dele, é de outra autoria é só se manifestar (o post esta aberto) que após a verificação, os créditos serão adicionados.

Dúvidas, criticas, contribuições, correções e adições serão bem vindas.