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′)
Here is the function:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- 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
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
3 thoughts on “Convert Time To Decimal (SQL)”
This is pretty good. I have been using the MySQL date_format() function. It is nice to know how to return that value in decimal.
vc é foda, AMEI sua função !