1. SQL Server, Datas e Horas

Tempo é uma coisa interessante. Dias, anos, horas, minutos, milissegundos… Em algum momento de sua vida com banco de dados você precisou, ou vai precisar, trabalhar com tempo. Datas e horas nos permitem realizar métricas, organizar compromissos, etc., e a maioria dos sistemas hoje em dia necessitam manipular data e hora.

O SQL Server fornece um amplo suporte para que você possa não só armazenar, mas realizar diversas operações como somar, ou subtrair, horas, dias, segundos, anos, ordenar e comparar data e hora! Porém, existe uma série de mitos e confusões relacionados a este assunto. Por exemplo, você é capaz de explicar o porquê isso acontece?

SELECT
	R.DataString
	,CONVERT(datetime,R.DataString) as DataDatetime
FROM
	(
		SELECT '20150314 23:59:59.999' as DataString
		UNION ALL
		SELECT '20150314 12:00:00.005'
	
	) R

Ao converter uma string para data, o SQL não apresentou o mesmo horário

O objetivo deste post é esclarecer como manipular datas dentro de seus scripts T-SQL.  Iremos abordar os seguintes assuntos:

1. Representando datas
2. Formatando a exibição
3. Tipos de dados de data

Por hoje, vamos focar na representação das datas, isto é, o modo como o SQL Server entende as datas que enviamos para ele.

Formato de Datas

Vamos começar falando do formato. Existe uma enorme confusão neste assunto. Primeiro, vamos deixar uma coisa bem clara: existem as datas que sua aplicação envia para o SQL Server, via código T-SQL, e as datas que o SQL Server envia para a sua aplicação. São duas coisas diferentes.

Fornecendo Datas para o SQL Server

Quando você envia um código T-SQL para o SQL Server, quem na verdade envia é uma aplicação. A aplicação é o que chamamos de client (por conta da arquitetura client-server).  Tudo que um client faz é pegar um código T-SQL, que não passa de texto (string), entregar ao SQL Server para ele processar, e esperar a resposta. Quando o client precisa fornecer uma data ao SQL Server ela vai junto com o código TSQL, como texto puro também.

Esta data que vai no código é o que podemos chamar de “formato de entrada“. Especificar uma data no SQL Server é muito simples: basta colocar a bendita entre aspas simples (‘). Isso mesmo jovem, você especifica datas igual especifica uma string qualquer dentro da linguagem T-SQL. O SQL Server só vai entender que aquilo é uma data, e não uma string qualquer, se ela for convertida para um tipo de dados de data, como por exemplo datetime, time, date, datetime2, etc. Essa conversão pode ser explícita ou implícita:

• Implícitas
  • Quando atribui para uma variável do tipo data

    DECLARE 
    	@MinhaData datetime
    ;
    
    SET @MinhaData = '20140101' --> Neste momento a string '20140101' será convertida para um formato de data e salvo na variável.
    
    --> Perceba a diferença na exibição. 
    SELECT '20140101' DataString, @MinhaData as DataDatetme

  • Quando atribui para uma coluna do tipo data

    -- Uma tabela simples
    CREATE TABLE cadastro( nome varchar(100), dataAniversario datetime );
    
    --															| Aqui é uma string. Mas ao inserir na coluna dataAniversario, ela será convertida para data.
    INSERT INTO cadastro(nome,dataAniversario) VALUES('Rodrigo','08/03/1992');
    
    SELECT * FROM cadastro;
    									
    UPDATE cadastro SET dataAniversario = '650311' WHERE nome = 'Rodrigo'
    --									|Aqui vai acontecer a conversão implítica de novo, quando o SQL atualizar a coluna.
    
    
    SELECT * FROM cadastro;

  • Quando compara com alguma expressão do tipo data

    -- Uma tabela simples
    CREATE TABLE cadastro( nome varchar(100), dataAniversario datetime );
    
    --															| Aqui é uma string. Mas ao inserir na coluna dataAniversario, ela será convertida para data.
    INSERT INTO cadastro(nome,dataAniversario) VALUES('Rodrigo','08/03/1992');
    INSERT INTO cadastro(nome,dataAniversario) VALUES('Thaís','01/01/1993');
    INSERT INTO cadastro(nome,dataAniversario) VALUES('Gustavo','09/12/2015');
    INSERT INTO cadastro(nome,dataAniversario) VALUES('Lucas','07/07/1965');
    
    --												--> Neste ponto, de novo, uma conversão implítica será feita da string para data, para que a comparação possa ser feita!
    SELECT * FROM cadastro WHERE dataAniversario >= '19930101'

• Explícitas
  • Quando converte usando CAST ou CONVERT para um tipo de data

    SELECT
    	CONVERT(datetime,'20140101')
    	,CONVERT(datetime,'19920101 13:00')
    	,CAST('20160101' as datetime)
    

     

Apesar do SQL Server parecer bem flexível quanto ao formato de entrada, você não pode colocar qualquer coisa. Não é a casa da mãe Joana não! Ao converter sua string para uma data, o SQL Server vai validar o formato e se não estiver em conformidade você irá receber um erro dizendo que não foi possível converter uma data a partir de uma string.

Este é o erro que você irá ver com mais frequência quando estiver trabalhando com datas e o formato estiver errado.

Essa string que contém uma data, que você coloca no código SQL, é chamada de Literal (ou constante) de data e pode ser interpretado de várias formas.  Aqui na documentação você tem todos os detalhes. O formato mais comum de um literal de data no SQL Server é:

A primeira coisa é entender os separadores. Basicamente, uma data é composta de várias partes: dia, mês, ano, hora, minutos, segundos e milissegundos. O SQL Server suporta barra (/), o ponto (.) e o traço como separadores para a parte dos dias. Para a parte das horas somente os dois-pontos (:) podem ser usados para separar horas, minutos e segundos. E somente o ponto pode ser usado para separar milissegundos. Todas as datas a seguir são válidas:

SELECT
	CONVERT(datetime,'02/02/2010')
	,CONVERT(datetime,'02.02.2010')
	,CONVERT(datetime,'02-02-2010')
	,CONVERT(datetime,'02-02-2010 12:23:45.010')

Se você especificar um separador diferente destes mencionados acima,  vai dar erro…

SELECT CONVERT(datetime,'02 02 2010')
GO
SELECT CONVERT(datetime,'02/02/2010 13.12.15.999')
GO

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Você pode omitir certas partes da data. Quando faz isso, o SQL Server considera a parte omitida como 0 (zero).  Se omitir a parte das horas, isto é, especificar somente dia, mês e ano, o horário será o mesmo que 00:00:00.000, isto é, meia noite em ponto! O horário tem de ser especificado no formato “HH:mm:SS.Milissegundos [AM|PM]“. Veja alguns exemplos:

SELECT 
	CONVERT(datetime,'01/01/2015 00:00:00.000')  --> Válido! Mesma coisa quando especifica somente a data
	,CONVERT(datetime,'01/01/2015 00:01:00.500') --> Válido
	,CONVERT(datetime,'01/01/2015 00:00:00') --> Válido. Você pode omitir os segundos e millisegundos. O SQL irá considerar como sendo 0.
	,CONVERT(datetime,'01/01/2015 13:57') --> Válido! Isso é o mesmo que 01/01/2015 13:57:00.000
GO

O tipo “datetime” permite que você especifique até 3 casas nos milissegundos. Os tipos time e datetime2 permitem até 7 casas! Depois falamos mais sobre os diferentes tipos de dados de data e hora. Por agora, concentre-se no formato. Outra  coisa interessante é que você pode especificar o formato 12 horas (AM/PM) nas horas!

SELECT 
	CONVERT(datetime,'01/01/2015 01:00AM') --> 1 da manhã
	,CONVERT(datetime,'01/01/2015 01:00PM') --> 1 da tarde
	,CONVERT(datetime,'01/01/2015 13:00PM') --> Mesmo no formato 24 horas, você consegue especificar o AM/PM.

O SQL Server não é idiota, portanto não tente um 13 da manhã…

SELECT
	CONVERT(datetime,'01/01/2015 13:00AM')

Curiosamente, isso é inválido e gera erro (omitir toda a parte do horário, exceto as horas):

SELECT CONVERT(datetime,'01/01/2015 13')
GO

Porém, se você especifica AM ou PM…

SELECT CONVERT(datetime,'01/01/2015 13PM') 
GO

Usar separadores de datas força o SQL Server a levar em consideração a região. Isto é, quando você usa um separador na parte dos dias, o SQL Server irá levar em consideração, por exemplo, o formato Brasil (dd/MM/AAAA) ou EUA (MM/dd/AAAA). A data abaixo pode ser 02 de fevereiro ou 01 de janeiro, dependendo de como o SQL Server está interpretando:

SELECT CONVERT(datetime,'01/02/2015')

E é aqui onde entra o comando SET DATEFORMAT. Este comando é muito polêmico. A maioria dos desenvolvedores acha que ele irá alterar a forma como a data é exibida. Pegadinha! Este comando apenas diz para o SQL Server como ele deve considerar o formato das datas de ENTRADA. Ele é a maneira de você dizer “SQL Server, estou te enviando as datas neste formato“.   Para saber qual o valor atual do dateformat, você pode executar este SELECT:

SELECT S.date_format FROM sys.dm_exec_sessions S WHERE S.session_id = @@SPID

O DATEFORMAT diz ao SQL Server onde está o dia, o mês, e o ano em sua data. Cada sessão possui seu dateformat, e não afeta o dateformat de outras sessões.

O DATEFORMAT padrão é definido quando você se conecta e é baseado nas configurações de idioma do login que você usa pra se conectar. Nós podemos alterar o DATEFORMAT a qualquer momento:

SET DATEFORMAT dmy; --> Dia/Mês/Ano

SELECT S.date_format FROM sys.dm_exec_sessions S WHERE S.session_id = @@SPID

SELECT
	CONVERT(datetime,'01/02/2015') --> O SQL entende isso como 01 de fevereiro!
	,CONVERT(datetime,'02/01/2015') --> O SQL entende isso como 02 de janeiro!
	,CONVERT(datetime,'13/01/2015') --> O SQL entende isso como 13 de janeiro!
GO

Lembre-se que o SET DATEFORMAT só irá afetar aquela sessão em que está se executando o comando. Quando você se desconectar (ou sua aplicação) do SQL Server, ou mesmo abrir outra sessão, o dateformat será baseado nas configurações do Login usado para a conexão.

Este aqui irá gerar erro, pois, conforme o dateformat configurado, não existe um 01 do 13…

SET dateformat dmy;
SELECT CONVERT(datetime,'01/13/2015')

Se você alterar o dateformat, deverá alterar o formato de suas datas:

SET DATEFORMAT mdy;  -->Mês/Dia/Ano

SELECT S.date_format FROM sys.dm_exec_sessions S WHERE S.session_id = @@SPID

SELECT
	CONVERT(datetime,'01/02/2015') --> O SQL entende isso como 02 de janeiro!
	,CONVERT(datetime,'02/01/2015') --> O SQL entende isso como 01 de fevereiro!
GO
--> Agora este dá erro, pois não existe 01 do 13 (o mês agora vem antes). Quando o DATEFORMAT era dmy funcionava...
SELECT
	CONVERT(datetime,'13/01/2015') 
GO

--> E este que gerava erro em dmy agora dá certo!
SELECT
	CONVERT(datetime,'01/13/2015') --> Agora dá certo, pois o mês é 01, e o dia 13. Isso é 13 de janeiro!
GO

No caso do ano, se você especificar o ano com 4 dígitos, o SQL Server detecta onde o ano está. Neste caso o dateformat vai valer somente para a posição do dia e do mês:

SET DATEFORMAT ydm; --> Ano/Dia/Mes

--> Funciona. O SQL Server sabe onde o ano está pelo número de dígitos
--> Porém, o dia e o mês continua valendo. Neste caso o dateformat diz "dia" antes de mês.
SELECT 
	CONVERT(datetime,'01/03/2010') 
	,CONVERT(datetime,'03/01/2010') 
GO

--> Se você usar 2 dígitos, ai a brincadeira muda...
SELECT 
	CONVERT(datetime,'01/03/20') --> isto significa: ano 01, dia 03, mês 20. Não existe mês 20, então dá erro!
GO

SELECT 
	CONVERT(datetime,'20/03/01') --> isto significa: ano 2020, dia 03, mês 01. 
GO

Sim, isso é um saco! Felizmente você pode contornar isso apenas deixando de usar os separadores. Sim, meu caro, quando não se usa separadores o SQL Server interpreta a sua data usando o formato ISO 8601 que é um formato padrão, independente do buraco do mundo onde você esteja!  Este formato sempre irá considerar AnoMêsDia ou AAAA/MM/DD ou AA/MM/DD. Sempre! Sempre! Independente do DATEFORMAT!

SET DATEFORMAT dmy;
SELECT CONVERT(datetime,'20130113') --> 13 de janeiro de 2013

SET DATEFORMAT mdy;
SELECT CONVERT(datetime,'20130113') --> 13 de janeiro de 2013. Aqui o dateformat não tem vez!
SELECT CONVERT(datetime,'920308') --> 08/03/1992! Perfeitamente válido! O SQL também entende ano com 2 dígitos!

E você pode continuar especificando as horas normalmente, já que é sempre o mesmo formato, independente da região:

SELECT 
	CONVERT(datetime,'20150101 13:45:09')
	,CONVERT(datetime,'20150101 01:02AM')
	,CONVERT(datetime,'20150101 00:02:03.123')

Eu gosto muito deste formato e sempre estou usando nos meus scripts. Mas você deve tomar muito cuidado pra não cometer alguns errinhos:

SELECT
	CONVERT(datetime,'201008') --> Isto não é 20 de outubro de 2008. Isto é  08 de outubro de 2020!
	,CONVERT(datetime,'191203') -->  Isso é 03 de Dezembro de 2019 e não  01 de março de 1912, ok? 
								-- Você pode alterar somente os dígitos do ano (2 dígitos ou 4 dígitos). As outras partes tem de ser com 2 dígitos!
GO

SELECT
	CONVERT(datetime,'15122010') --> Isso seria 10/20/1512. Ano correto, dia correto, porém não existe mês 20 jovem...

Fique atento ao tipos de dados também…

SELECT
	CONVERT(datetime2,'15120110') --> Com os tipos date e datetime2, o ano 1512 é facilmente aceitável...
GO

--> Isso dá erro! O tipo datetime já não suporta 1512 e o comando abaixo dá pau!
-- Tome cuidado! Fique atento ao range de datas suportados pelo tipo escolhido!
-- Essa dica vale tanto para quando usar separadores, tanto quando não usar!
SELECT
	CONVERT(datetime,'15120110')

Bom, para deixar você respirar um pouco, vou encerrar por aqui. Na próxima semana posto a continuação! Fique ligado no blog!

Rodrigo Ribeiro Gomes

DBA Team Leader na Power Tuning

Posts Relacionados:

2. SQL Server, Datas e Horas: Exibição e Conversões 3. SQL Server, Datas e Horas: Tipos de dados Instância DEFAULT e conectividade não default – Parte 1 Entendendo o que é um banco de dados e o que faz um Administrador de Banco de dados (DBA) Cuidados ao interpretar o Logical Reads no SQL Server