Computing SUM on DATETIME datatype
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 'XYZ' spends 45min for activity 'A' and say 1hr 25 min for activity 'B' and user 'PQR' spends 1hr 20min for activity 'A' n 2hr 55min for activity 'B' then i want to calculate the 'SUM' of 'TIME_SPENT' for the users which should appear as 'Total time spent =2:10' for user 'XYZ' n 'Total time spent =4:15'
Can somebody pls help me with this?
Thnx in advance.
[814 byte] By [
technowerk] at [2007-11-11 7:39:46]
# 1 Re: Computing SUM on DATETIME datatype
G'day mate...
Try something like this:
SELECT DATEADD(n, q.mins, CAST('00:00' AS DATETIME))
FROM (
SELECT SUM(DATEDIFF(n, CAST('00:00' AS DATETIME), mydateField)) AS mins
FROM myTable
) q
hth,
Cheers,
Paul
# 2 Re: Computing SUM on DATETIME datatype
hey thnx for ur reply Paul,
ur code works great except for the format of the output it generates.
The format of the output is as follows-
'1900-01-01 02:15:00.000'
I do not want the date to be displayed in the output. Is there any way by which i can get rid of the date in the output n only display the sum of the time spent for the activities?
Thnx once again.
# 3 Re: Computing SUM on DATETIME datatype
Sure...
Change this line:
SELECT DATEADD(n, q.mins, CAST('00:00' AS DATETIME))
To this:
SELECT CONVERT(VARCHAR, DATEADD(n, q.mins, CAST('00:00' AS DATETIME)) , 108)
That will cut it down to just hh:mm:ss.. Of course if the sum of the time spent is >= 1 day you will need to change that...
Cheerio,
P
# 4 Re: Computing SUM on DATETIME datatype
thnx once again for ur reply Paul,
ur code works great to produce the output in the format i want. But as u said earlier it fails if sum(time_spent) exceeds 24hrs. What can i do to avoid such situation? :confused:
I strictly do not want the date to appear in the output, at the same time i want to display the sum(time_spent) in hrs. only, even if it exceeds 24hrs.
The output should be something like:
Time Spent=195:45hrs
where 195 is in hrs. n 45 is in min.
I'd highly appreciate if u can help me with this.
thnx once again for ur guidance so far.
# 5 Re: Computing SUM on DATETIME datatype
G'day... This should do the trick ;-)
SELECT CASE WHEN DATEPART ( d, q.dte) > DATEPART( d, CAST('00:00' AS DATETIME))
THEN CAST(DATEDIFF( d, CAST('00:00' AS DATETIME), q.dte) AS VARCHAR) + ' '
ELSE '' END
+ CONVERT(VARCHAR, DATEADD(n, q.mins, CAST('00:00' AS DATETIME)) , 108)
FROM (
SELECT DATEADD(n, SUM(DATEDIFF(n, CAST('00:00' AS DATETIME), myDateField)) , CAST('00:00' AS DATETIME) ) as dte,
SUM(DATEDIFF(n, CAST('00:00' AS DATETIME), myDateField) ) as mins
FROM myTable
) q
HTH!
Cheers,
Paul
# 6 Re: Computing SUM on DATETIME datatype
G'day... This should do the trick ;-)
SELECT CASE WHEN DATEPART ( d, q.dte) > DATEPART( d, CAST('00:00' AS DATETIME))
THEN CAST(DATEDIFF( d, CAST('00:00' AS DATETIME), q.dte) AS VARCHAR) + ' '
ELSE '' END
+ CONVERT(VARCHAR, DATEADD(n, q.mins, CAST('00:00' AS DATETIME)) , 108)
FROM (
SELECT DATEADD(n, SUM(DATEDIFF(n, CAST('00:00' AS DATETIME), myDateField)) , CAST('00:00' AS DATETIME) ) as dte,
SUM(DATEDIFF(n, CAST('00:00' AS DATETIME), myDateField) ) as mins
FROM myTable
) q
HTH!
Cheers,
Paul
Dear PaulMcM please tell me the feild name of output query "No Colum Name" from the above query (sum(Datetime))
OR How can i pass this value to another as an argument to vb.net
thanks
i really impressed ....thanks again ...
M_Umar at 2007-11-11 23:52:45 >
