Jump to content

Archived

This topic is now archived and is closed to further replies.

jothaz

Manipulando Data no SQL Server - (contéudo alterado)

By jothaz, in SQL Server

Recommended Posts

jothaz    1

jothaz
Posted

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 @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!

 

 

[*]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 MINUTO
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/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_ANOS
De 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/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

Considerações:

[*]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.

Share this post

Link to post
Share on other sites

Rodrigo Miss    1

Rodrigo Miss
Posted

jothaz, ótimo esse seu tópico ...

 

kra, talvez eu esteja fazendo alguma "nhaca"... é que naum so acostumado a manipular data em sql server ...

to fazendo o seguinte select:

 

 

SELECT CONVERT(VARCHAR(10),DTACADASTRO,103)
FROM ENTIDADES WHERE CONVERT(VARCHAR(10),DTACADASTRO,103) BETWEEN 
CONVERT(VARCHAR(10),'09/01/2003',103) AND CONVERT(VARCHAR(10),'10/01/2003',103) ORDER BY DTACADASTRO

 

ele me retorna os registros q estao com data 09/01/2003 e 10/01/2003 mais junto com isso ele tbm me traz registros com a data : 09/12/2005 ... pq isso sera?

Share this post

Link to post
Share on other sites

eriva_br    7

eriva_br
Posted

jothaz, ótimo esse seu tópico ...

 

kra, talvez eu esteja fazendo alguma "nhaca"... é que naum so acostumado a manipular data em sql server ...

to fazendo o seguinte select:

 

 

SELECT CONVERT(VARCHAR(10),DTACADASTRO,103)
FROM ENTIDADES WHERE CONVERT(VARCHAR(10),DTACADASTRO,103) BETWEEN 
CONVERT(VARCHAR(10),'09/01/2003',103) AND CONVERT(VARCHAR(10),'10/01/2003',103) ORDER BY DTACADASTRO

 

ele me retorna os registros q estao com data 09/01/2003 e 10/01/2003 mais junto com isso ele tbm me traz registros com a data : 09/12/2005 ... pq isso sera?

Falai Rodrigo,

 

Como esta sendo convertido para texto a comparação esta sendo feita por texto e não de data..., o ideal seria setar a conexão para o formado DMY, desta forma não seria necessário os converts.

Para setar a conexão:

set dateformat dmy
t+

Share this post

Link to post
Share on other sites

eriva_br    7

eriva_br
Posted

pessoal, e teria como saber se o dia de hoje cai em um final de semana, e qual seria este dia? sabado ou domingo... Precisava para manipular infos de um relatório.

 

utilize a função DATEPART e verifique se é 1-Domingo ou 7-Sábado

exemplo:

set nocount on
DECLARE @Today DATETIME
SET @Today = '20101123'

SET LANGUAGE brazilian
SELECT DATENAME(dw, @Today-1) AS 'Ontem', DATENAME(dw, @Today) AS 'Hoje', DATENAME(dw, @Today+1) AS 'Amanhã'
SELECT DATEPART(dw, @Today-1) AS 'Ontem', DATEPART(dw, @Today) AS 'Hoje', DATEPART(dw, @Today+1) AS 'Amanhã'

SET LANGUAGE us_english
SELECT DATENAME(dw, @Today-1) AS 'Yesterday', DATENAME(dw, @Today) AS 'Today', DATENAME(dw, @Today+1) AS 'Tomorrow'
SELECT DATEPART(dw, @Today-1) AS 'Yesterday', DATEPART(dw, @Today) AS 'Today', DATEPART(dw, @Today+1) AS 'Tomorrow'
GO
retorna:

Changed language setting to Português (Brasil).
Ontem                          Hoje                           Amanhã
------------------------------ ------------------------------ ------------------------------
Segunda-Feira                  Terça-Feira                    Quarta-Feira

Ontem       Hoje        Amanhã
----------- ----------- -----------
2           3           4

Changed language setting to us_english.
Yesterday                      Today                          Tomorrow
------------------------------ ------------------------------ ------------------------------
Monday                         Tuesday                        Wednesday

Yesterday   Today       Tomorrow
----------- ----------- -----------
2           3           4

dias: 

1 - Monday
2 - Tuesday
3 - Wednesday
4 - Thursday
5 - Friday
6 - Saturday
7 - Sunday
 (default, U.S. English, Brazilian as well)
fonte: http://msdn.microsoft.com/en-us/library/ms181598.aspx

 

cheers!

Share this post

Link to post
Share on other sites
Go To Topic Listing SQL Server
×

Important Information

Ao usar o fórum, você concorda com nossos Terms of Use.

  I accept