Pular para o conteúdo principal

Como converter e formatar a exibição de horas decimais com SQL Server - parte 1

Esta publicação demonstra como converter números decimais usados para armazenar duração de tempo para a representação no formato de horas e minutos usando as funções do SQL Server 2008.

O objetivo é permitir representar números armazenados no formato decimal no banco de dados para facilitar o cálculo usando-se o formato padrão para horas e minutos, HH:MM, assim, por exemplo, 1,5 que refere-se à duração de uma hora e meia poderá ser representado como 01:30.

Como a proposta deste blog é manter as coisas simples e principalmente, atender a um público que está iniciando na programação, não serão considerados nos cálculos a duração em segundos e centésimos para que os cálculos fiquem simples. A versão mínima do SQL Server para poder executar este exemplo é a 2008 R2 sendo que é possível usar a sua versão gratuita (SQL EXPRESS EDITION).

Nesta primeira parte serão demonstrados os passos necessários para realizar as conversões de base numérica. Na segunda, será dado o exemplo de uma função definida pelo usuário (UDF) no banco SQL Server para ser usada com as consultas.

Os códigos que estão demonstrados funcionam e foram testados apenas para o banco de dados SQL Server. Cada gerenciador de banco de dados possui uma maneira diferente para tratamento de armazenamento em variáveis e criação de funções do usuário.

Uma questão de base

É muito comum nas tarefas dos programadores ter de fazer cálculos com horas em algum momento. Pode ser para cálculo de valores em moeda baseado na quantidade do tempo trabalhado ou estimativas de custos baseados em horas, não importa. O que todos os cálculos envolvendo tempo corrido terão em comum é o fato de que as horas precisarão ser convertidas da base sexagimal para a decimal para que os cálculos sejam feitos.

É mais comum que todas as horas sejam representadas na base sexagimal. Os múltiplos e submúltiplos são obtidos multiplicando ou dividindo por sessenta. Assim, uma hora é igual a sessenta minutos e cada minuto corresponde à sessenta segundos. Se é necessário calcular-se o número total de minutos de uma hora, basta multiplicar o número por sessenta.

Porém para os cálculos de base financeira não é possível usar números com base sessenta diretamente. Se for necessário calcular o valor final de um serviço que durou, por exemplo, 01h30m, este número precisa ser convertido para a base decimal obtendo-se o valor 1,5 que pode então ser multiplicado pelo valor unitário.

Conversão de base sexagimal para base decimal

Esta conversão consiste em transformar o tempo representado no formato tradicional HH:MM para um número decimal para que possa ser armazenado e se façam cálculos.

Tomando o exemplo de 01:30 de duração deve-se armazenar cada parte do tempo em uma variável inteira em um primeiro passo, assim teremos uma variável inteira para hora e outra para os minutos, considerando o uso da linguagem SQL:

 declare @horas as decimal(6, 2) = 1;
 declare @minutos as decimal(6, 2) = 30;
Atenção
Um ponto importante que deve ser observado se ao desenvolver o exemplo se estiver usando o SQL Server é que as variáveis precisam ser declaradas do tipo DECIMAL com pelo menos duas casas após o ponto. O motivo disto é que se não for feito desta forma, a conversão não será feita corretamente.

Para se ter o número completo na base decimal deve se dividir os minutos por sessenta (60) e somar o valor encontrado com as horas, assim, obtendo-se a duração final em base decimal. Novamente, se for fazer este cálculo com a linguagem SQL teremos este código:

 declare @minutos_sexagimais as decimal(6,2) = @minutos / 60;
 declare @tempo as decimal(6,2) = @horas + @minutos_sexagimais;

Conversão de base decimal para base sexagimal

Esta tarefa consiste em obter o valor que está armazenado em base decimal e poder converter para a sua representação no formato sexagimal (HH:MM). Para fazer isto é necessário que alguns passos sejam seguidos, o primeiro é armazenar apenas o valor correspondente às horas Isto é feito lendo o valor do tempo e atribuindo para uma variável do tipo inteiro. O resultado é que a parte decimal será desprezada:

declare @h as int = 1.50;

O segundo passo é obter os minutos convertidos para a base sexagimal. Isto envolve dois passos:

  1. Multiplicar o valor total do tempo por sessenta para se obter o valor total em minutos.
  2. Armazenar em uma variável do tipo inteiro o resto da divisão do valor do tempo em minutos por sessenta usando o operador de resto de divisão (módulo)

O exemplo abaixo demonstra como isto pode ser feito.

declare @minutos as int = 1.50 * 60 % 60; -- resultado: 30

O operador de módulo no SQL Server é o sinal de percentagem "%" seguido do divisor.

Com os passos expostos até aqui já é possível considerar como fazer para se obter os valores. Falta agora fazer as formatações corretas e tornar tudo isto utilizável facilmente através da definição de uma UDF. Isto será tratado no próximo post. Até lá!


Postagens mais visitadas deste blog

Como gerar scripts para exportar dados no SQL Server 2008

Uma das tarefas mais comuns no trabalho com desenvolvimento de software que consome dados em bancos como o SQL Server 2008 é a necessidade de em algum momento precisarmos exportar os dados de um banco para outro. Quer seja para realizar testes ou fazer simulações existem várias maneiras de se fazer isto. Neste post eu quero demonstrar um recurso do SQL Server Management Studio (SSMS) que permite realizar esta tarefa rapidamente.Para os que estão acostumados a usar esta ferramenta, já devem saber que é possível gerar scripts para o schema e também transferir os dados entre dois bancos distintos. Isto pode ser feito se o SSMS puder conectar-se com as duas bases, de origem e destino. No exemplo que vou dar, o objetivo é gerar o script apenas para uma tabela do banco de dados de exemplo da Microsoft – Northwind.1. Iniciando o assistenteO assistente deve ser iniciado clicando com o botão direito do mouse sobre o banco onde se encontra a tabela a qual iremos gerar o script. Deve se clicar n…

Pivot dinâmico com SQL Server

Passo a passo para usar pivoteamento dinâmicoOs bancos de dados bem configurados e definidos armazenam os dados de forma a otimizar o acesso, evitando duplicidade e garantindo a integridade. Porém, em muitas situações isto pode dificultar a apresentação de forma adequada sendo necessário preparar os dados usando vários recursos entre os quais, fazer o pivoteamento.Se você não precisou ainda usar ou não sabe o que é consiste em transformar cada linha de uma determinada coluna em colunas de uma nova consulta.Assim, considere uma tabela que armazene as notas bimestrais de um boletim. Uma possível estrutura para esta tabela seria algo assim:ColunaTipo de dado/TamanhoDisciplinanvarchar(50)BimestreIntNotanumeric(5,2)Uma consulta select nesta tabela com alguns dados traria um resultado parecido com o abaixo:Porém pode ser que para apresentar estes dados em um relatório seja necessário transformar cada bimestre em uma coluna e agrupar as notas nestas colunas para que fique dessa forma:Isto po…

Como remover espaços e quebra de linha de documentos XML

A dica de hoje (após um longo e tenebroso inverno, que a propósito continua aqui no Paraná) nasceu de um incêndio que precisou ser apagado nos projetos da vida com prazo de entrega pra “ontem”… mas, vamos a parte divertida.No Framework .NET a geração de documentos XML é facilitada de várias formas. Vamos partir do código abaixo para gerar um documento simples usando apenas System.Xml.var xmlDoc = new XmlDocument();
XmlElement raiz = xmlDoc.CreateElement("artigo");
XmlElement Id = xmlDoc.CreateElement("id");
Id.InnerText = "08072011";
XmlElement Titulo = xmlDoc.CreateElement("titulo");
Titulo.InnerText = "Como remover espaços e quebra de linha de documentos XML";
XmlElement Vazio = xmlDoc.CreateElement("vazio");
Vazio.InnerText = String.Empty;
raiz.AppendChild(Id);
raiz.AppendChild(Titulo);
raiz.AppendChild(Vazio);
xmlDoc.AppendChild(raiz);
xmlDoc.Save(@"d:\teste.xml");

O código acima gera o arquivo “teste.xml” que ao ser visuali…