SQL Server -  T-SQL - Trabalhando com data e hora

Este artigo aborda o tratamento de datas e hora pelo SQL Server (estou usando a versão 2012 Express ) de uma forma suscinta, prática  e objetiva.

 

O SQL Server possui vários tipos diferentes para tratar data e hora com diferentes níveis de intervalo e precisão (e diferentes níveis de espaço de armazenamento requeridos).

 

Possui também numerosas funções para retornar, modificar e validar os dados a partir destes tipos de dados em vários formatos.

 

Abaixo vemos uma tabela que mostra vários tipos de data/hora para o SQL Server:

 

Tipo de Dados Formato Intervalo Precisão Tamanho Armazenamento (bytes)
Time hh:mm:ss[.nnnnnnn]

00:00:00.0000000 até  23:59:59.9999999

 100 nano segundos 3 até 5
Date YYYY-MM-DD 0001–01–01 até  9999–12–31 1 dia 3
Smalldatetime YYYY-MM-DD hh:mm:ss 1900–01–01 até 2079–06–06 1 minuto 4
DateTime YYYY-MM-DD hh:mm:ss[.nnn] 1753–01–01 até 9999–12–31 0,00333 segundo 8
datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn]

0001–01–01 00:00:00.0000000  até  9999–12–31 23:59:59.9999999

 100 nano segundos 6 até 8
Datetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn][+|-]hh:mm

0001–01–01 00:00:00.0000000 até 9999–12–31 23:59:59.9999999 (in UTC)

 100 nano segundos 8 até 10

 

Vamos agora mostrar algumas funções que tratam data e hora usando a sintaxe T-SQL.

 

Estou usando o SQL Server Management Studio Express Edition :

 


Para executar as consultas abra o SQL Server Management Studio, efetue o login e a seguir clique em New Query ou tecle CTRL+N.

 

Será aberta a janela para você digitar a consulta T-SQL.

 

Após digitar clique em Execute e veja o na janela logo abaixo.

 

Agora vamos ao trabalho:

 

 1- Retornando a data e hora atual

 

Para retornar a data e hora atual podemos usar as funções GETDATE, GETUTCDATE, CURRENT_TIMESTAMP, SYSDATETIME, SYSUTCDATETIME, or SYSDATETIMEOFFSET
 

- GETDATE e CURRENT_TIMESTAMP retornam a data e hora local em um tipo de dados datetime.

- GETUTCDATE retorna a hora UTC também no tipo de dados datetime.

- SYSDATETIME retorna a data e hora local no tipo de dados datetime2.

- SYSUTCDATETIME retorna a hora UTC também no tipo de dados datetime2.

- SYSDATETIMEOFFSET retorna a hora local mais o numero de horas e minutos de deslocamento do UTC no tipo de dados datetimeoffset.

 

 

UTC - significa 'Universal Time Coordinate" ou 'Tempo Universal Coordenado' ,  também conhecido como tempo civil, é o fuso horário de referência a partir do qual se calculam todas as outras zonas horárias do mundo. Corresponde à hora de inverno de Portugal Continental e Arquipélago da Madeira e à hora de verão do Arquipélago dos Açores.

 

É o sucessor do Tempo Médio de Greenwich (Greenwich Mean Time), cuja sigla é GMT. A nova denominação foi cunhada para eliminar a inclusão de uma localização específica num padrão internacional, assim como para basear a medida do tempo nos padrões atômicos, mais do que nos celestes. ( http://pt.wikipedia.org/wiki/Tempo_Universal_Coordenado)

 
 2- Incrementando e decrementando valores em uma data

 

Para adicionar ou subtrair um intervalo de data ou hora em um valor para data utilize a função DATEADD.

 

SELECT DATEADD(YEAR, -1, '2013-04-02T00:00:00'); -> Subtrai um ano da data informada

SELECT DATEADD(YEAR, +1, '2014-04-02T00:00:00'); -> Adiciona um ano à data informada

SELECT DATEADD(Day, 1, '2014-04-29T00:00:00'); -> Adiciona um dia da data

SELECT DATEADD(Month, -1, '2014-04-29T00:00:00'); -> Subtrai um mês da data

SELECT DATEADD(hour , +1, '2014-04-29T00:00:00'); -> Adiciona uma hora à data

 

 

Sintaxe :  DATEAD (datepart, número, data)

 

Retorna uma data especificada com o intervalo número especificado (inteiro com sinal)  adicionado à datepart especificada dessa data.

 

A função DATEADD  usa 3 parâmetros :

 

1- O primeiro parâmetro é a parte da data a modificar. Os valores usados estão na tabela abaixo:

DatePart Abreviação
Year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
Day dd, d
week wk, ww
weekday dw, w
hour hh
minute min, n
second ss, s
milisecond ms
microsecond mcs
nanosecond ns

2- O segundo parâmetro é o valor numérico para o número de de unidades dateparts que estamos adicionando/subtraindo do valor da data. (valores negativos subtraem as unidades das datas).

3- O terceiro parâmetro é a data a ser modificada.

Nota:
- O tipo de dados de retorno é o tipo de dados do argumento date, com exceção de literais de cadeia de caracteres.
- O tipo de dados de retorno para um literal de cadeia de caracteres é datetime.
- Um erro será gerado se a escala de segundos do literal de cadeia de caracteres tiver mais de três posições (nnn) ou contiver a parte de deslocamento do fuso horário.
 

 3 - Encontrando a diferença entre duas datas

 

Para calcular a diferença entre duas datas utilize a função DATEDIFF.
 

Sintaxe : DATEDIFF ( datepart , startdate , enddate )

 

A função retorna a contagem (inteiro com sinal) dos limites especificados de datepart cruzados entre os parâmetros especificados startdate e enddate.

 

Neste exemplo eu estou usando o banco de dados Northwind.mdf e calculando a diferença entre a data de hoje e data armazenada no campo ShippedDate da tabela Orders em dias.

 

Sintaxe: 
DATEDIFF ( datepart , startdate , enddate )

A função DATEDIFF aceita três parâmetros:

  1. datepart - usado para identificar se você esta contando a diferença em termos de dias, horas, meses, etc;
  2. A data inicio;
  3. A data fim;

 

Note que a função retorna o número de limites datepart cruzados o que não é a mesma coisa que o tempo decorrido entre as duas datas.

A seguir temos dois exemplos de consultas onde cada coluna retorna a quantidade de um limite datepart cruzado para cada datepart especificado, embora a diferença entre as duas datas seja de 100 nano segundos (0,000001 segundos).

SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

 
Exemplo de consulta Exemplo de Consultas usando diferentes dateparts.

- Cada datepart e suas abreviações retornam o mesmo valor.
- Se o valor de retorno estiver fora do intervalo de int (-2,147,483,648 a +2,147,483,647), um erro será retornado.
- Para millisecond, a diferença máxima entre startdate e enddate é de 24 dias, 20 horas, 31 minutos e 23.647 segundos.
- Para second, a diferença máximo é de 68 anos.
- Se a startdate e enddate forem atribuídos apenas um valor de hora e o datepart não for um datepart de hora, será retornado 0.
- Se apenas um valor de hora for atribuído a uma variável de tipo de dados 'data', o valor da parte 'data' faltante será definido como o padrão: 1900-01-01

 4- Exibindo o valor string de parte de uma data

 

Para obter o nome do mês e dia da semana para uma data específica utilize a função DATENAME.

 

A função DATENAME retorna uma cadeia de caracteres que representa o datepart especificado da date especificada.

 

Sintaxe:  DATENAME ( datepart , date )

 

O valor do retorno depende do ambiente de idioma definido usando SET LANGUAGE.

 

No exemplo abaixo a consulta SQL retorna o nome do mês e o nome do dia da semana para o campo RequiredDate da tabela Orders do banco de dados Northwind.mdf.

 

Note que usamos a instrução SET LANGUAGE definindo o idioma como Português.

 

- A configuração de SET LANGUAGE é definida no momento da execução e não no momento da análise.

- SET LANGUAGE define implicitamente a configuração de SET DATEFORMAT.

 5- Determinando o último dia do mês

 

Para saber o último dia do mês de uma data utilize a função EOMONTH.

 

A função EOMONTH retorna o último dia do mês que contém a data especificada com um deslocamento opcional.

 

Sintaxe:    EOMONTH ( start_date [, month_to_add ] )

start_date
Expressão de data que especifica a data para a qual retornar o último dia do mês.
month_to_add
Expressão de inteiro opcional que especifica o número de meses a adicionar a start_date.

Se esse argumento for especificado, EOMONTH adicionará o número de meses especificado a start_date e retornará o último dia do mês da data resultante. Se essa adição exceder o intervalo de datas válido, um erro será lançado.

Na consulta SQL baixo usamos a função EOMONTH para obter o último dia do mês de uma data para o mês atual e para o mês seguinte.

Essa função pode ser remota para servidores SQL Server 2012 e posteriores. Ela não pode ser remota para servidores com versão anterior a SQL Server 2012.

E com isso concluímos esta etapa mostrando algumas funções T-SQL para tratar datas no SQL Server. 

 

Aguarde outro artigo sobre o assunto em breve.

Filipenses 2:14 Fazei todas as coisas sem murmurações nem contendas;

Filipenses 2:15 para que vos torneis irrepreensíveis e sinceros, filhos de Deus imaculados no meio de uma geração corrupta e perversa, entre a qual resplandeceis como luminares no mundo,

Filipenses 2:16 retendo a palavra da vida; para que no dia de Cristo eu tenha motivo de gloriar-me de que não foi em vão que corri nem em vão que trabalhei.
Veja os Destaques e novidades do SUPER DVD Visual Basic (sempre atualizado) : clique e confira !

Quer migrar para o VB .NET ?

Quer aprender C# ??

 

             Gostou ?   Compartilhe no Facebook   Compartilhe no Twitter

Referências:

José Carlos Macoratti