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.
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.
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
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 MINUTOMais exemplos e particularidades de cada função pró-atividade e Books Online(procure por Date and Time Functions).
7. Datas e matemática. É possível?
-- --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_ANOSDe novo, novamente! Mais exemplos e particularidades de cada função pró-atividade e Books Online(procure por Date and Time
Functions).
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 #ATIVIDADESConsiderações:
1. Peço a colaboração de todos para melhorarmos este post.
AUTOR: "Jothaz" - o material deste post foi compilado a partir dos Books Online, matérial de outros fóruns, da internet e das
surras que eu já levei por causa das danadas das datas.
Pronto ai está!
Esse tutorial me ajudou muito, espero que sirva para vcs tbm. Dúvidas,
criticas, contribuições, correções e adições serão bem vindas.
Edição feita por: Renan L. Queiroz, 13/02/2009, 09:16.