CONVERTING HOURS, MINUTES AND SECONDS TO MINUTES

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 2000 Forums

Transact-SQL (2000)

CONVERTING HOURS, MINUTES AND SECONDS TO MINUTES

Author

Topic

Olad

11 Posts

Posted - 2008-10-12 : 04:23:52

Hello Everyone

I am new to SQL 2000 and would like to covert a column in the format of 00:00:00 (hours, minutes and seconds) to total minutes. The column data type is nvarchar.

Regards

Gulled

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-12 : 04:30:10

[code]SELECT LEFT(yourcol,2)*60+SUBSTRING(yourcol,4,2)+RIGHT(yourcol,2)/60.0 AS TotalDurationinMins
FROM Table[/code]

however one question remains

why are you storing date values in varchar field. this makes date manipulations difficult as you cant use standard date functions for it.Always use proper datatype for storing values.

Olad

11 Posts

Posted - 2008-10-12 : 04:59:30

quote:
Originally posted by visakh16

SELECT LEFT(yourcol,2)*60+SUBSTRING(yourcol,4,2)+RIGHT(yourcol,2)/60.0 AS TotalDurationinMins
FROM Table

however one question remains

why are you storing date values in varchar field. this makes date manipulations difficult as you cant use standard date functions for it.Always use proper datatype for storing values.

Thank you very much for your prompt reply. Is there anyway I can then sum the resultset and save the time to sum it using EXCEL?

The database was designed by someone else and I asked myself the same question about the nvarchar datatype.

Gulled

Gulled

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-12 : 05:08:49

by sum the resultset do you mean sum all record values and get a single value? If you really want to use excel to sum this post the question in some excel forums. But didnt understand why you think its best to do calculation there?

Olad

11 Posts

Posted - 2008-10-12 : 08:40:49

quote:
Originally posted by visakh16

by sum the resultset do you mean sum all record values and get a single value? If you really want to use excel to sum this post the question in some excel forums. But didnt understand why you think its best to do calculation there?

Yes, I meant summing all values in the column to get a single value.

Gulled

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-12 : 08:47:52

SELECT DATEDIFF(SECOND, '00:00:00', yourcol) / 60.0E0 AS TotalDurationInMinutes
FROM Table

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

Olad

11 Posts

Posted - 2008-10-12 : 09:22:34

quote:
Originally posted by Peso

SELECT DATEDIFF(SECOND, '00:00:00', yourcol) / 60.0E0 AS TotalDurationInMinutes
FROM Table

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

Peso

This query gives me the same resultet as above which is very good. I now know two ways to convert hours, minutes and seconds into my beloved minutes. But I would also like to sum all the minutes in the column to get one single value. I have
30.000000
30.000000
30.000000
30.000000
30.000000
30.000000

Is there anyway I can sum all these to get one value? In other words, I want sum up TotalDurationInMinutes.

Gulled

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-12 : 09:38:21

SELECT SUM(DATEDIFF(SECOND, '00:00:00', yourcol) / 60.0E0) AS TotalDurationInMinutes
FROM Table

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

Olad

11 Posts

Posted - 2008-10-12 : 09:58:42

quote:
Originally posted by Peso

SELECT SUM(DATEDIFF(SECOND, '00:00:00', yourcol) / 60.0E0) AS TotalDurationInMinutes
FROM Table

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

Peso

Great..every thing is working now. I really thank you and appreciate your help.

Gulled

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources