Convting 00:00 into mintues or seconds

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Convting 00:00 into mintues or seconds

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2009-03-25 : 08:02:10

Hi all,

How do I convert time (CHAR dataype) available in format MM:SS into minutes or seconds
e.g 30:52 should be converted to 30.(52 x 60)30.3120 mins

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-25 : 08:15:31

30:52 is 30 minutes and 52 seconds which also is 1852 seconds total?

E 12°55'05.63"
N 56°04'39.26"

umertahir
Posting Yak Master

154 Posts

Posted - 2009-03-25 : 08:21:38

Yes, either converting whole to minutes or to seconds is required.

quote:
Originally posted by Peso

30:52 is 30 minutes and 52 seconds which also is 1852 seconds total?

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-25 : 08:23:01

Shouldn't it be 30.(52/60)=30.866666 rather ?

umertahir
Posting Yak Master

154 Posts

Posted - 2009-03-25 : 08:26:32

oops...yes you're right...

quote:
Originally posted by sakets_2000

Shouldn't it be 30.(52/60)=30.866666 rather ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-25 : 08:29:59

This is rare.
A person asks for help and doesn't know what he or she wants...

DECLARE	@Sample TABLE
	(
		theTime CHAR(5)
	)

INSERT	@Sample
SELECT	'30:52'

SELECT	theTime,
	60 * theHours + theMinutes AS TotalMinutes,
	theHours + theMinutes / 60.0E AS FractionalHours
FROM	(
		SELECT	theTime,
			PARSENAME(REPLACE(theTime, ':', '.'), 2) AS theHours,
			PARSENAME(REPLACE(theTime, ':', '.'), 1) AS theMinutes
		FROM	@Sample
	) AS d

E 12°55'05.63"
N 56°04'39.26"

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-25 : 08:33:52

For seconds


DECLARE @STR VARCHAR(10)
SET @STR='30:52' 
SELECT  Datediff(ss,0,CONVERT(DATETIME,'00:'+@STR,108)) as sec

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

umertahir
Posting Yak Master

154 Posts

Posted - 2009-03-25 : 08:39:29

Thanks guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources