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.
| Author |
Topic |
|
andybeh
Starting Member
2 Posts |
 Posted - 2009-09-13 : 18:23:25
|
| Hi,I know straight up that I have set up a field in my table incorrectly, so I am after some advice on fixing this and then calculating the data. I have a field which is set to varchar(8) and stores the length of time for an exercise session. This allows me to store the data as hh:mm:ss and I don't have dates or AM/PM to worry about. The problem is that now I am ready to start summing the total of time spent exercising each month and I am unsure of how I can do this. I did consider using a Split function to separate the values and then calculate the hours, minutes and seconds as total seconds and then convert back to hh:mm:ss but Split functions always seem to create new rows in a table, and not columns in a row. Does anyone have a good suggestion for handling this?Cheersab |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-09-14 : 01:53:02
|
SELECT SUM(DATEDIFF(SECOND, 0, CAST(Col1 AS DATETIME)))
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
andybeh
Starting Member
2 Posts |
Posted - 2009-09-14 : 23:37:01
|
| Thanks for that it worked well although it rounds to the nearest minute. I used the solution to calculate straight seconds and I'll do the minutes and seconds calculations in ASP. |
|
|
|
|
|
|
Calculating time