Ir para conteúdo
Fórum Script Brasil
  • 0

Datas desmistificadas em SQL


jothaz

Pergunta

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.

Link para o comentário
Compartilhar em outros sites

0 respostass a esta questão

Posts Recomendados

Até agora não há respostas para essa pergunta

Participe da discussão

Você pode postar agora e se registrar depois. Se você já tem uma conta, acesse agora para postar com sua conta.

Visitante
Responder esta pergunta...

×   Você colou conteúdo com formatação.   Remover formatação

  Apenas 75 emoticons são permitidos.

×   Seu link foi incorporado automaticamente.   Exibir como um link em vez disso

×   Seu conteúdo anterior foi restaurado.   Limpar Editor

×   Você não pode colar imagens diretamente. Carregar ou inserir imagens do URL.



  • Estatísticas dos Fóruns

    • Tópicos
      152k
    • Posts
      651,7k
×
×
  • Criar Novo...