Ricardo Parente's Blog

I was following the SQL list and came across a post from Jim Gotwald where he showed his solution to convert a time string into a decimal value (ex: ’10:30′ to 10.5).

So I decided to write a custom function where you can either pass a complete date as a string or just the time and it will return a float value representing the time in decimal.

You may call the function like this:

select dbo.timeToDecimal(cast(getDate()) as varchar(20))
select dbo.timeToDecimal(’2008-06-18 10:35:43′)
select dbo.timeToDecimal(’06/18/2009 10:22:00′)
select dbo.timeToDecimal(’10:42′)

Here is the function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Ricardo Parente
-- Create date: 2009-06-18
-- Description:    I convert a time string to decimal
-- Parameter: datetime string (varchar) or just a time string
-- =============================================
CREATE FUNCTION dbo.timeToDecimal
(
@timeToConvert varchar(25)
)
RETURNS float
AS
BEGIN
DECLARE @Result float
DECLARE @t varchar(5)
SET @t = cast(datepart(hh,@timeToConvert) as varchar(2)) + ':' + cast(datepart(n,@timeToConvert) as varchar(2))
SET @Result = CAST(DATEDIFF(N, '00:00', @t) AS FLOAT)/60
RETURN @Result
END
GO