Pular para o conteúdo principal

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

Este artigo é uma continuação da primeira parte onde são explicados os passos para a conversão de horas no formato decimal para o padrão para horas (HH:MM).

O objetivo é demonstrar como criar uma função do SQL Server para representar o tempo corrido em horas e minutos no formato correto uma vez tendo armazenado este valor em formato decimal para fins de cálculo. Eu recomendo a leitura do artigo acima para que se tenha uma visão geral do assunto e o seu pano de fundo.

Inicialmente pode parecer um trabalho desnecessário criar uma função específica para esta finalidade principalmente por que a função CONVERT possui vários recursos que possibilitam realizar a tarefa acima, mas, quando se começa a explorar as possibilidades, percebe-se que o trabalho de achar o formato correto dos parâmetros e o número de cálculos que se tem de executar incluindo conversões em cascata, chamadas de diversas funções e operações diversas com datas é praticamente o mesmo (ou maior até) do que criar uma função apropriada para esta finalidade.

Além disso, o propósito é também demonstrar a criação de funções de usuário com o SQL Server e usar estas em consultas.

Criando a função para conversão

Desde versões mais antigas o SQL Server (assim como demais bancos de dados) era fornecido com funções para auxiliar durante as consultas. Atualmente existe um número muito grande (que pode ser conferido neste link). Além de sua grande utilidade é possível estender as funcionalidades da linguagem SQL com funções definidas pelo usuário ou User Defined Functions. Novamente, a documentação oficial pode ser de grande utilidade para a criação destas.

Basicamente você pode criar uma função com dois tipos de retorno:

  1. Valores simples ou scalar values que é o termo usado pela documentação oficial.
  2. Tabelas ou conjunto de colunas e linhas que correspondam a um resultado de uma consulta.

No exemplo proposto será criada uma função do tipo scalar que precisa receber como parâmetro um número do tipo decimal correspondendo ao tempo corrido e retorna um valor do tipo string representando as horas e minutos da duração convertidos.

O corpo desta função está representado a seguir.


create function dbo.DecimalToHour(@elapsed_hours as decimal(8,2)) returns varchar(8)
as
begin
 declare @sHour as varchar(4) = convert(varchar(4), convert(integer, @elapsed_hours))
 declare @sMin as varchar(2) = convert(varchar(2), convert(integer, @elapsed_hours * 60 % 60))
 return replicate('0', 2 - len(@sHour)) + @sHour + ':' + replicate('0', 2 - len(@sMin)) + @sMin;
end
go

O primeiro ponto a observar é o cabeçalho da função. Observe que foi incluida a palavra reservada "dbo" ligada ao nome. Isto tem, no exemplo, o objetivo de indentificar que a função é de usuário e não uma função nativa do banco.

Continuando com o cabeçalho entre parênteses foi nomeado o parâmetro que é recebido para a execução do cálculo. Este segue o padrão da criação de variáveis do SQL Server. O tipo decimal foi o que melhor se adequou à função. A definição da escala e da precisão também são necessárias para este exemplo. Neste caso somente uma variável foi usada, mas, como você pode esperar, várias podem ser definidas.

Finalizando a declaração da função deve ser colocado o tipo de dado de retorno correspondente aos tipos suportados pelo SQL Server. No exemplo da conversão foi escolhido o tipo varchar com um tamanho máximo de oito posições.

O corpo da função é desenvolvido dentro de um bloco as ... begin ... end e a última instrução deve ser obrigatoriamente ser um return .

A primeira linha declara uma variável para armazenar a parte correspondente às horas. Neste passo basta apenas usar a conversão do tipo de dados decimal para integer que o valor é truncado sem arredondamentos e aí está a necessidade de se seguir exatamente os tipos de dados e tamanhos que estão demonstrados nesta função. Esta função prevè a exibição de horas de duração com até quatro casas, para durações maiores é necessário alterar o tamanho definido em varchar.

Na sequência é feita a conversão dos minutos usando o operador de módulo do SQL Server. Esta operação foi explicada em detalhes no post anterior então aqui a única ação adicional foi a conversão para o formato apropriado de retorno.

Para o retorno as duas variáveis são concatenadas e formatadas com zeros à direita através da função replicate. Note que está sendo calculado o número de repetições considerando apenas duas posições.

Demonstrando a utilização

A forma mais rápida de testar o funcionamento é após criar a função em seu banco de dados executar uma instrução como a que está demonstrada no exemplo abaixo.


select dbo.DecimalToHour(23.5);

Esta instrução vai gerar o resultado da figura abaixo.

Para um exemplo completo vamos considerar uma tabela fictícia e bem simples para que armazene quantas horas cada desenvolvedor trabalhou em um projeto. Esta tabela proposta pode ter a estrutura demonstrada a seguir.

Considere ainda os dados da próxima figura como pertencentes a esta tabela.

A consulta a seguir demonstra como fica uma consulta usando uma função definida pelo usuário, é importante observar que foi colocado o nome do schema "dbo" para que a função seja corretamente acessada.


select DeveloperName, ProjectName, dbo.DecimalToHour(WorkedHours) as WorkedTime
from Projects
order by ProjectName
rollback
go

Estes resultados podem variar dependendo dos dados que você inserir. É possível aperfeiçoar esta função ainda para fazer outros cálculos como retornar o número de dias também. Fica a dica para você explorar. Até a próxima!


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…