Usamos cookies para medir audiência e melhorar sua experiência. Você pode aceitar ou recusar a qualquer momento. Veja sobre o iMasters.
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. http://forum.imasters.com.br/public/style_emoticons/default/blush.gif
[*]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.000SELECT CAST(GETDATE() AS DATETIME)----Retorna Data do sistema completa (com arredondamento): AAAA-MM-DD 00:00:00SELECT CAST(GETDATE() AS SMALLDATETIME)---- Exemplos do Books Online-- --Retorna: 2000-05-08 12:35SELECT 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:36SELECT 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.
[*]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)[*]A forma como o banco de dados tratará/gravará/exibrá o campo data:
---- Exemplos do Books OnlineSET DATEFORMAT mdyDECLARE @datevar datetimeSET @datevar = '12/31/98'SELECT @datevarSET DATEFORMAT ydmSET @datevar = '98/31/12'SELECT @datevarSET DATEFORMAT ymdSET @datevar = '98/12/31'SELECT @datevarNo post: **[http://forum.imasters.com.br/index.php?showtopic=223962](http://forum.imasters.com.br/index.php?showtopic=223962)** nosso companheiro **[eriva_br](http://forum.imasters.com.br/index.php?showuser=16361)** dá uma aula sobre o assunto. Vale uma conferida! [*]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.
[*]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', --mm/dd/aaaaCONVERT(VARCHAR(12),GETDATE(),102) AS '102', --aa.mm.ddCONVERT(VARCHAR(12),GETDATE(),103) AS '103', --dd/mm/aaaaCONVERT(VARCHAR(12),GETDATE(),104) AS '104', --dd.mm.aaCONVERT(VARCHAR(12),GETDATE(),105) AS '105', --dd-mm-aaCONVERT(VARCHAR(12),GETDATE(),106) AS '106', --dd mês aaCONVERT(VARCHAR(12),GETDATE(),107) AS '107', --Mês dd, aaCONVERT(VARCHAR(12),GETDATE(),108) AS '108', --hh:mi:ssCONVERT(VARCHAR(12),GETDATE(),109) AS '109', --mês dd aaaa hh:mi:ss:mmmAM (ou PM)CONVERT(VARCHAR(12),GETDATE(),110) AS '110', --mm-dd-aaCONVERT(VARCHAR(12),GETDATE(),111) AS '111', --aa/mm/ddCONVERT(VARCHAR(12),GETDATE(),112) AS '112', --aammddCONVERT(VARCHAR(12),GETDATE(),113) AS '113', --dd mês aaaa hh:mi:ss:mmm (24h)CONVERT(VARCHAR(12),GETDATE(),114) AS '114', --hh:mi:ss:mmm(24h)CONVERT(VARCHAR(12),GETDATE(),120) AS '120', --aaaa-mm-dd hh:mi:ss(24h)CONVERT(VARCHAR(12),GETDATE(),121) AS '121' --aaaa-mm-dd hh:mi:ss.mmm(24h)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 memso 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.[*]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 dataSELECT 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 dataSELECT 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). http://forum.imasters.com.br/public/style_emoticons/default/assobiando.gif
[*]Datas e matemática. É possível? http://forum.imasters.com.br/public/style_emoticons/default/devil.gif
----DATEDIFF retorna um INTEITO como resultado de operações--entre dua datasDECLARE @DT_EXEMPLO DATETIMESET @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). [http://forum.imasters.com.br/public/style_emoticons/](http://forum.imasters.com.br/public/style_emoticons/)default/assobiando.gif
[*]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_MINUTOSFROM #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_DEPOISFROM #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_REALFROM #ATIVIDADES DROP TABLE #ATIVIDADES[*]Esta é a primeira versão prometo efetuar as alterações e correções necessárias bem como incluir mais exemplos e comentários.
[*]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 aqui 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.
Carregando comentários...