sum of hours mins and secs - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

sum of hours mins and secs

Author

Topic

limericklad1974
Starting Member

39 Posts

Posted - 2012-09-20 : 04:43:07

Hi there
I have a column called 84-94 and in it there is date in the following format: 10/09/2012 00:35:40
I also have a column called 95-100 and again in it, there is data in the following format: 10/09/2012 00:42:43
I have a colum next to it called TimeAboveAT and I would like to sum the data from the two other columns and place the total hrs:mins:secs in this column

Can anyone help with a piece of code I can run which would do this? I am struggling massively with time formats!
Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-20 : 04:47:57

how do you sum up the 2 date & time ?

10/09/2012 00:35:40 & 10/09/2012 00:42:43

what is the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

limericklad1974
Starting Member

39 Posts

Posted - 2012-09-20 : 04:51:37

If you sum the two columns, you would get 77 mins and 83 secs, but this is actually 1 hour, 8 mins and 23 secs, so I would need to display 01:08:23 in the TimeAboveAT column

limericklad1974
Starting Member

39 Posts

Posted - 2012-09-20 : 04:52:17

The date is irrelevant, it is only hrs:mins:secs I am looking to sum, hope that clears things up

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-20 : 05:18:30

[code]declare @dt1 datetime = '10/09/2012 00:35:40',
@dt2 datetime = '10/09/2012 00:42:43'

select dateadd(hour, datepart(hour, @dt1) + datepart(hour, @dt2),
dateadd(minute, datepart(minute, @dt1) + datepart(minute, @dt2),
dateadd(second, datepart(second, @dt1) + datepart(second, @dt2), 0)))[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-20 : 05:19:24

if you want it in HH:MM:SS string, just convert the result of the above query using convert(varchar(8), < result > , 108)

KH
[spoiler]Time is always against us[/spoiler]

limericklad1974
Starting Member

39 Posts

Posted - 2012-09-20 : 05:37:46

Sorry, I am unsure how to run youer code.... If I copy and paste your code into a query window and run it, I get the following error message

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@dt1".

I am running SQL Server 2005

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-09-20 : 06:06:25

Slightly modified the declaration part

declare @dt1 datetime,@dt2 datetime

set @dt1= '10/09/2012 00:35:40'
set @dt2 = '10/09/2012 00:42:43'

select dateadd(hour, datepart(hour, @dt1) + datepart(hour, @dt2),
dateadd(minute, datepart(minute, @dt1) + datepart(minute, @dt2),
dateadd(second, datepart(second, @dt1) + datepart(second, @dt2), 0)))

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

limericklad1974
Starting Member

39 Posts

Posted - 2012-09-20 : 06:30:44

ok, many thanks, this seems to do the trick!

limericklad1974
Starting Member

39 Posts

Posted - 2012-09-20 : 08:16:18

sorry, still struggling....

I have a table called Data and I want to update each row in this table, so that the hours:mins:secs from the two columns I mentioned are summed into the column called TimeAboveAT

Is there a piece of code such as
Update Data
Set TimeAboveAT = (Sum of time in '80-94' and '95-100')

that you can help me with?

I have nearly 1300 rows of data in the table!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-20 : 08:22:33

replace @dt1 and @dt2 with your column name

UPDATE Data
SET    TimeAboveAT = dateadd(hour, datepart(hour, [80-94]) + datepart(hour, [95-100]), 
	                 dateadd(minute, datepart(minute, [80-94]) + datepart(minute, [95-100]),
                 		dateadd(second, datepart(second, [80-94]) + datepart(second, [95-100]), 0)))

KH
[spoiler]Time is always against us[/spoiler]

limericklad1974
Starting Member

39 Posts

Posted - 2012-09-20 : 09:07:15

yes, thats it, thanks a million!

limericklad1974
Starting Member

39 Posts

Posted - 2012-09-20 : 09:17:47

hi, my next problem with hours:mins:secs is to do with averages:
say I have three rows in a database table clumn called TimeAboveAT - the values are
01:12:45
00:34:27
00:19:24

I can use your code to sum them, but if I wanted to get an average and display it in hrs:mins:secs, can you help me with this?

The sum of the times above is 1 hr, 65 mins and 96 secs, which is equal to 02:06:36 in the prope time format
The average of the three times above would be 42 mins and 12 secs, which I would need displayed as 00:42:12

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-20 : 09:43:27

[code]SELECT dateadd(second, 0, avg(datediff(second, 0, TimeAboveAT)))[/code]

KH
[spoiler]Time is always against us[/spoiler]

limericklad1974
Starting Member

39 Posts

Posted - 2012-09-20 : 10:53:15

sorry, not sure how your code is to be used....

I have put it into Query Analyser in the following format
select * from data
dateadd(second, 0, avg(datediff(second, 0, TimeAboveAT)))

and I got the following error message

Msg 321, Level 15, State 1, Line 4
"second" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

Am I not using your code correctly? I would like to get the average of the values in a certain column in a table called Data

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-20 : 10:58:00

[code]
SELECT dateadd(second, 0, avg(datediff(second, 0, TimeAboveAT)))
FROM data[/code]

KH
[spoiler]Time is always against us[/spoiler]

limericklad1974
Starting Member

39 Posts

Posted - 2012-09-21 : 06:45:07

sorry again, I am struggling to understand what you mean in your code...
let me try to explain further the make up of my SQL table.

The table is called Data
In it, there are 4 columns:
ID
PersonID
Date
TimeAboveAT

I am trying to get the average for each person and the result needs to be in the format hrs:mins:secs e.g. 01:12:42

In the future, I would like to be able to pass in some dates and get the average for a person between 1 date and the other

limericklad1974
Starting Member

39 Posts

Posted - 2012-09-21 : 06:46:03

when I run your current code, I get
1902-05-10 00:00:00.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-21 : 10:16:03

try


SELECT PersonID, dateadd(second, 0, avg(datediff(second, 0, TimeAboveAT)))
FROM   data
GROUP BY PersonID

KH
[spoiler]Time is always against us[/spoiler]

limericklad1974
Starting Member

39 Posts

Posted - 2012-09-21 : 10:27:35

no, I sitll don't get proper results....

I get a list with the person id in column 1 and the 2nd column has data such as:
1902-11-30 00:00:00.000
1902-06-21 00:00:00.000
1901-08-05 00:00:00.000

somehow you are getting data back but its getting added to the date rather than the time format...

I appreciate all your help so far, thank you!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources