Jump to content
Fórum Script Brasil
  • 0
Sign in to follow this  
jothaz

Datas desmistificadas em SQL

Question

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.

Share this post


Link to post
Share on other sites

0 answers to this question

Recommended Posts

There have been no answers to this question yet

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  



  • Forum Statistics

    • Total Topics
      148393
    • Total Posts
      643786
×
×
  • Create New...