Adding columns with time values and convert it to hr, min and sec

[lostty84]

Hi,
I have three columns, hour(s), min(s), sec(s)

I can add it up, but will like to convert it into. hrs, mins and sec.

this is how, i am adding it up into seconds.

  SELECT  ((TotalTimeSpentHrs*60*60)+(TotalTimeSpentMin*60)+(TotalTimeSpentSec))AS totaltime
FROM EST1 

how can I convert it, I ma using sql server. thanks

[molona]

Since you have this information in three different fields, one way to do this is treating this info as text to show it in the format you want (which, I guess, it will be hh:mm:ss)

Then you’ll be able to convert to whatever time you want (how to do this conversion depends on the database you use and you should refer to their manual)

[r937]

SELECT CONVERT(TIME, CAST(TotalTimeSpentHrs AS CHAR(2)) + ':' + CAST(TotalTimeSpentMin AS CHAR(2)) + ':' + CAST(TotalTimeSpentSec AS CHAR(2)),108) AS totaltime FROM EST1

[molona]

I didn’t see that he was using SQL Server until your wrote this lol

[lostty84]

I AM GETTING

Conversion failed when converting date and/or time from character string.

[molona]

What type of data do you use for each field?

[lostty84]

the columns are (float, null)

[r937]

[quote=“lostty84, post:6, topic:102251, full:true”]
I AM GETTING

Conversion failed when converting date and/or time from character string.
[/quote]try this –

SELECT CONVERT(TIME,'12:15:23',108) AS test_time

if this works, is it possible that you have one of those three column values out of range?

SELECT 'yes, bad data' FROM EST1 WHERE TotalTimeSpentHrs > 23 OR TotalTimeSpentMin > 59 OR TotalTimeSpentSec > 59
if this returns 0 rows, then probably the CAST from float to CHAR(2) didn’t work…

[lostty84]

Good morning, ]

the test time query works, it returns the test time

12:15:23.0000000

and the second query works, and it returns two rows
yes, bad data
yes, bad data

[r937]

[quote=“lostty84, post:10, topic:102251, full:true”]and the second query works, and it returns two rows
yes, bad data
yes, bad data
[/quote]well, there’s your reason for the failed conversion

looks like you will have to compute total seconds after all, and then convert the final number back to a time

[lostty84]

i have used this to convert to seconds

UPDATE EST SET CurTotalTimeUsed = (TotalTimeSpentHrs*60*60) + (TotalTimeSpentMin*60)+(TotalTimeSpentSec)

how can i convert it to hrs, min , and secs.

cheers

[r937]

[quote=“lostty84, post:12, topic:102251, full:true”]how can i convert it to hrs, min , and secs.[/quote]use math, in the other direction…

divide by 3600, that’s hours
divide the remainder by 60, that’s minutes
divide the remainder of that by 60, that’s seconds