I was working on a data warehouse project where the time data (HH:MM) were coming in decimal format from the source. Some of them were restricted to actual hour and minutes (23.59) and some were coming in real decimal format like (16.80). So I had a tough time dealing with the situation to make it standardised before storing into the warehouse and in time format. As usual I searched over on internet and did not find any good help, then I created my own function on basis of few calculation.
CREATE FUNCTION [dbo].[DecimalToTime](@input DECIMAL(18,2), @flg BIT = 1) RETURNS TIME(0) AS BEGIN --@flg = 0 When decimal value are in time format Eg: 12.30, Output: 12:30 --@flg = 1 When you want to convert decimal to time Eg: Input: 12.90, Output: 12:54 DECLARE @result AS TIME(0) IF @flg = 1 SELECT @result = CONVERT(TIME(0), DATEADD(MINUTE, 60 * (ISNULL(@input, 00.00)), 0)) ELSE SELECT @result = CAST(RIGHT('0' + REPLACE(CAST(@input AS VARCHAR(10)), '.', ':'), 5) AS TIME) RETURN @result END