Monthly archives "March"

2 Articles

SQL Server, Datas e Horas – Parte 2: Exibição e Conversões

Olá! No último post, focamos em como o SQL Server entende as datas que enviamos no código T-SQL. Para resumir: Especificar datas no SQL Server é como especificar uma string qualquer, porém com um formato específico. Você pode usar o DATEFORMAT para auxiliar na interpretação do SQL Server (e não como a data é exibida) ou usar formatos genéricos, como o ISO. Bom, agora vamos falar sobre a exibição das datas, ou seja, o formato de saída!

Vamos começar alinhando um ponto: O SQL Server não exibe nada! Ele entrega data à alguma aplicação (algum driver de conexão como jTDS, ODBC,OLEDB, etc.). Internamente, uma data é bem diferente do formato como especificamos. A data não é armazenada como texto. É um formato binário bem diferente: imagine que seja um número muito louco que de alguma forma contenha os dias, meses, anos, hora, etc. (prometo um post sobre isso em breve). Ao efetuar um SELECT que retorne uma coluna com algum, por exemplo, datetime, o SQL Server não entrega uma string para a aplicação. Ele entrega este número (binário). A aplicação é responsável por interpretar este valor e exibi-lo da forma correta, ou seja, a aplicação também entende onde está o dia, mês, ano e horário nesse número. (O excel também trabalha de forma parecida. Experimente digitar este valor 42071,1588859 e formatar a célula como data…)

No caso do Management Studio (SSMS), quando você visualiza os resultado em um GRID (padrão), ele vai exibir as datas no formato ‘YYYY-MM-AA HH:MM:SS.mmm’. Dependendo do tipo de dado (date, time, datetime2, etc.) ele vai omitir a data ou o horário. Perceba (execute no SSMS):

A questão aqui é: a aplicação é a verdadeira responsável por exibir a data em um formato mais apropriado.

Caso você não queria que a aplicação faça estre trabalho você pode forçar o SQL Server a entregar uma data como uma string, ao invés deste número binário maluco… Em outras palavras, você pode converter uma data para uma string. Bom aqui cabe deixar uma coisa clara: quando você converte uma data para string, aquela data perde seus poderes mágicos: comparar com outras datas, adicionar datas, subtrair, ordenar. A data convertida passa a ser tratada como texto puro e perde certos comportamentos exclusivos de data. Se sua aplicação não vai precisar realizar nenhum processamento extra com aquela data e se o seu objetivo é apenas exibição, então não há problemas! Observe os exemplos a seguir para entender melhor. Para converter uma data para string você usa a função CONVERT. Ela permite que você especifique um parâmetro especial chamado Style (A função CAST também pode ser usada, mas não permite especificar o Style). O Style é um número pré-determinado que faz com que o resultado da conversão saia em um formato específico. A sintaxe geral é:

CONVERT(varchar(tamanho),  ValorData, Style)

Primeiro, você deve estar atento ao tamanho da string (pode ser varchar ou char) que irá receber o resultado. Se o seu objetivo é converter a data para o formato dd/MM/AAAA então você vai precisar de 2 caracteres para o dia, 2 para o mês, 4 para o ano e mais 2 para as barras, totalizando 10 caracteres. Mas se você quiser incluir as horas e minutos (dd/MM/AAAA HH:MM) então, além dos 10, você vai precisar de 1 para espaço, 2 para as horas, 1 para os dois-pontos e 2 para os minutos, isto é, mais 6 caracteres, o que faz necessário 16 caracteres para a data inteira. Então fique atento a isso para colocar o tamanho correto, ou você irá ver sua data cortada… O Style é um valor fixo e pré-determinado que você pode encontrar aqui na documentação desta função. ValorData pode ser qualquer coisa que seja uma data. Em outras palavras, ValorData pode ser qualquer expressão que resulte em um tipo de data: funções de sistema (DATEADD,CURRENT_TIMESTAMP, etc.), funções de usuário, nomes de variável, nomes de colunas, etc. Bom,  a documentação é sua melhor fonte. Este site também contém uma série de exemplos de uso da função e irá te ajudar quando precisar de uma resposta rápida. No mais, alguns exemplos:

Uma coisa interessante é que podemos notar o que foi falado aqui com o uso do comando DBCC OUTPUTBUFFER. Basicamente, este comando nos permite ver os dados que uma determinada sessão entregou para a aplicação. Execute esta query:

Agora abra outra sessão e execute o comando DBCC OUTPUTBUFFER, passando como parâmetro o número da sessão onde a query anterior foi executada (no meu caso, foi 51):

Você deverá um resultado parecido com este (irei manter somente o início, que é o relevante pra nós neste momento):

00000040 04 f0 00 36 00 d1 0d 00 41 4e 54 45 53 5f 44 41 .ð.6.Ñ..ANTES_DA
00000050 5f 44 41 54 41 0a 00 32 38 2f 30 33 2f 32 30 31 _DATA..28/03/201
00000060 35 0e 00 44 45 50 4f 49 53 5f 44 41 5f 44 41 54 5..DEPOIS_DA_DAT
00000070 41 fd 11 00 c1 00 01 00 00 00 00 00 00 00 81 03 Aý..Á………..
00000080 00 00 00 00 00 20 00 a7 0d 00 09 04 f0 00 36 00 ….. .§.. .ð.6.
00000090 00 00 00 00 21 00 6f 08 00 00 00 00 00 20 00 a7 ….!.o…… .§
000000a0 0e 00 09 04 f0 00 36 00 d1 0d 00 41 4e 54 45 53 .. .ð.6.Ñ..ANTES
000000b0 5f 44 41 5f 44 41 54 41 08 69 a4 00 00 d9 73 0e _DA_DATA.i¤..Ùs.
000000c0 01 0e 00 44 45 50 4f 49 53 5f 44 41 5f 44 41 54 DEPOIS_DA_DAT

Primeiro, repare no trecho em verde. Este é o trecho que corresponde ao primeiro SELECT, onde nós usamos um CONVERT para converter a data para texto. Perceba que o valor retornado é realmente um texto puro, e por isso podemos visualizar facilmente os caracteres correspondentes. Agora, repare o trecho em azul. Este trecho corresponde ao segundo SELECT, onde nós retornamos a data pura. Notou!? Cadê a data? Ela está ali, só que está sendo entregue em um formato binário, cujo seus bytes não são equivalentes aos digitos e barras, conforme no trecho anterior. O trecho em binário que corresponde à data está marcado em laranja, apenas por curiosidade (no post que eu prometi sobre data, eu darei mais detalhes 🙂 ).

Para finalizar esta parte, um último ponto: Os poderes mágicos da data. Uma das grandes vantagens em ter tipos de dados que trabalhem exclusivamente com datas é a facilidade que temos em lidar com operações aritméticas envolvendo datas, como por exemplo, somar dias, meses, horas, minutos e segundo. Assim, o SQL Server é quem se preocupa em fazer todas as verificações quando, por exemplo, você adiciona 1 minuto à data “28/02/2016 23:59:59.003”.  Ordenar datas também é um diferencial: Por exemplo, considere as datas ’10/02/2015′ e ’01/03/2015′. Claramente, se eu te pedisse para ordenar estas datas de forma crescente, você, uma pessoa muito esperta, me diria assim:

’10/02/2015′
’01/03/2015′

Que está correto! O SQL Server também faria exatamente a mesma coisa, se estes valores estiverem sendo trabalhados como data… Agora olha só o que acontece quando você converte para texto e ordena pelo resultado da conversão:

Aqui, os resultados:

OrdenadoPorData
2015-02-10 00:00:00.000
2015-03-01 00:00:00.000

OrdenadoPorTexto
2015-03-01 00:00:00.000
2015-02-10 00:00:00.000

Repare que no segundo SELECT, a data de março veio ANTES da data de fevereiro, em um ORDER BY crescente 😯 ! Será um bug? Nada disso. Quando você converteu para texto e ordenou pelo resultado em texto, as regras de ordenação mudaram, pois para texto são diferentes das regras para data.  Veja:

Resultados:

OrdenadoPorTexto        Convertido
2015-03-01 00:00:00.000 01/03/2015
2015-02-10 00:00:00.000 10/02/2015

O motivo é simples:  A string ’01/03/2015′ começa com ‘0’ (dígito zero), que conforme as regras de ordenação de texto (onde envolve collations e outras coisas) é menor do que ‘1’.

Entendeu o porquê deve-se tomar cuidado com as conversões de data para texto? No próximo post, último desta série, vamos falar sobre os tipos de dados… Aproveitando o gancho, você saberia explicar o porquê disto?

 

Se não, aguarde o próximo post (Que tentarei publicar na semana que vem).

[]’s

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?

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

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 datetimetimedatedatetime2, etc. Essa conversão pode ser explícita ou implícita:

  • Implícitas
    • Quando atribui para uma variável do tipo data
    • Quando atribui para uma coluna do tipo data
    • Quando compara com alguma expressão do tipo data
  • Explícitas
    • Quando converte usando CAST ou CONVERT para um tipo de data

       

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.

SQL Server Error 241

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 é:

Formato Geral de Literal de Datas

 

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:

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

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:

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!

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

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

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

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:

SET DATEFORMAT não é pra exibição!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:

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:

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…

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

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:

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!

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

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

Fique atento ao tipos de dados também…

 

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

 

[]’s