how to convert Time format HH:MM:SS to decimal point

Question

_{Thursday, December 13, 2012 8:02 AM}

Hi to all,

Just want to ask if there is a way to convert this time format hh:mm:ss to decimal

so meaning decimal point hours.minutes

for example:

1.) 00:00:36 = 0.6

hope someone can help me.

thanks in advance.

All replies (8)

_{Thursday, December 13, 2012 8:11 AM ✅Answered | 1 vote}

Hi,

See if below posts are helpful to you

http://caulfli.hubpages.com/hub/SQL-2005-functions-to-convert-Time-to-decimal-and-decimal-to-Time

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/f30a69cd-d940-4e99-ae70-e9c8e5b3334c

- Chintak (My Blog)

_{Thursday, December 13, 2012 8:09 AM}

Hi Iamnewtosql,

this may work for you.

DECLARE   @time   datetime = '00:00:36'
SELECT  @time, CAST(@time AS float) * 864

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

_{Thursday, December 13, 2012 8:11 AM}

create table #t (t time)
insert into #t values ('00:00:36')

select datepart(SECOND,cast(t as datetime))/6.0*0.1  from #t

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

_{Thursday, December 13, 2012 8:34 AM}

See this example

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/f30a69cd-d940-4e99-ae70-e9c8e5b3334c

Many Thanks & Best Regards, Hua Min

_{Thursday, December 13, 2012 8:35 AM}

Hi

I think this is wrong

lets say 03:46:12 it should be 3.77

can someone help me here? much appreciated.

thanks in advance.

_{Thursday, December 13, 2012 8:41 AM}

Try

declare @hms varchar(8)
set @hms = '03:46:12'
declare @hours decimal(14,2)
declare @minutes decimal(14,2)
set @minutes = datepart(minute, @hms)
set @hours = datepart(hour, @hms)+@minutes/60
select @hours

Many Thanks & Best Regards, Hua Min

_{Thursday, December 13, 2012 9:17 AM | 1 vote}

Hallo,

than this may work for u:

DECLARE   @time   datetime = '03:46:12'
SELECT  @time, CAST(@time AS float),
        CAST(DATEPART(hh, @time) AS float) +
        CAST(DATEPART(mi, @time) AS float) / 60 +
        CAST(DATEPART(ss, @time) AS float) / 3600

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

_{Sunday, May 22, 2016 11:26 PM}

if cell A1 contains 13:21:00

in cell A2 format as a number and type =A1*24