Computing 'SUM' on 'DATETIME' datatype

[technowerk]

hi everybody,

i'm trying to calculate the 'SUM' of time spent in hrs. n min. How can i do this using SQL Server?
What i mean is, i've a column 'TIME_SPENT' that has 'datetime' datatype. This column saves time spent for an activity in format 'hh:mm'. Suppose a user spends 45min for activity 'A' and say 1hr 25 min for activity 'B' then i want to calculate the 'SUM' of 'TIME_SPENT' for the user which should appear as 'Total time spent =2:10'

Can somebody pls help me with this?

Thnx in advance.

[exterminator]

The SQL Server SUM function can only be used with numeric columns. And you have a datetime column.. so there is a problem using it. What you can do is make a UDF that would return the total number of seconds given the datetime value from this field (hh:mm:ss format) and then you can apply a SUM over that returned value (formatted column, as you may say it). And after the SUM call you can apply another UDF (written by you) to format the result into the hh:mm:ss format. So, in all you need these two UDFs to be written - ConvertToSeconds() and ConvertToDateTime(). Here's how you write a UDF on SQL Server - SQL Server - User Defined Functions (http://msdn.microsoft.com/msdnmag/issues/03/11/DataPoints/). Here's another article about them - SQL Server UDFs (http://www.sqlteam.com/item.asp?ItemID=979). Do some research before writing them yourself to see if they already exist (I am not sure, probably you would need to write them yourself). Hope this helps. Regards.

[technowerk]

thnx for ur quick reply exterminator,

For the convenience i've changed the datatype to 'int' so tht the users enter the time spent in minutes. This makes it easy to calculate the sum of 'time_spent'. Now suppose in earlier eg. i want to generate a report that will return 'total time spent=2:15'. For this if i use '/' operator, it returns 2 n if i use '%' operator, it returns 15. Which operator can i use to generate desired result?

thnx once again.

[exterminator]

For the convenience i've changed the datatype to 'int' so tht the users enter the time spent in minutes. This makes it easy to calculate the sum of 'time_spent'.Yes. This is a much neater way to do something like this. I thought of suggesting you this but didn't...i dont know why.. but it's good that you figured it out yourself. :thumb:
Now suppose in earlier eg. i want to generate a report that will return 'total time spent=2:15'. For this if i use '/' operator, it returns 2 n if i use '%' operator, it returns 15. Which operator can i use to generate desired result?You lost me here! How do you use these operators? The way I suggested previously is also easy .. defining two simple UDFs and calling them over a specified column... Regards.

[minhchau]

The SQL Server SUM function can only be used with numeric columns. And you have a datetime column.. so there is a problem using it. What you can do is make a UDF that would return the total number of seconds given the datetime value from this field (hh:mm:ss format) and then you can apply a SUM over that returned value (formatted column, as you may say it). And after the SUM call you can apply another UDF (written by you) to format the result into the hh:mm:ss format. So, in all you need these two UDFs to be written - ConvertToSeconds() and ConvertToDateTime(). Here's how you write a UDF on SQL Server - SQL Server - User Defined Functions (http://msdn.microsoft.com/msdnmag/issues/03/11/DataPoints/). Here's another article about them - SQL Server UDFs (http://www.sqlteam.com/item.asp?ItemID=979). Do some research before writing them yourself to see if they already exist (I am not sure, probably you would need to write them yourself). Hope this helps. Regards.
Can u poss some detail stored procedure ??? i'm a newbie coder :) thanx

[hspc]

You can create a UDF like :
Create function dbo.TimeFromMinutes(@minutes int)
returns char(5)
As
Begin
declare @hours int
declare @mins int
set @hours=@minutes / 60
set @mins=@minutes % 60

return cast(@hours as char(2))+':'+cast(@mins as char(2))

End
and call it :
Select dbo.TimeFromMinutes(220)
Select ID,dbo.TimeFromMinutes(My_Minustes_Column_Name)

[minhchau]

thank a lot !