Loop and Time - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Loop and Time

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2006-11-07 : 13:00:52

Does anybody have one example of Loop in stored procedure ...
Actually, I need pull time column, summarize time(translate into minute) ....

I want to change this statement from vb application to stored procedure

SELECT [myTime] from myTable where ID = 'x'
set Recordset = new adodb.recordset
For e = 0 To .RecordCount - 1
f = Format((Recordset!myTime), "Short Time")
hours = Format((f), "HH")
minute = Format((f), "nn")
i = (hours * 60) + minute
if i = 0 then
y = 0
else
y = i
end if
Next e

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 13:04:08

What data type is [myTime]? What are myTime values?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 13:09:08

Maybe something like this?

SELECT	CONVERT(varchar, SUM(myTime), 108)
FROM	myTable
WHERE	ID = 'x'

Peter Larsson
Helsingborg, Sweden

ilimax
Posting Yak Master

164 Posts

Posted - 2006-11-07 : 13:55:35

Format of [myTime] is long time "7/8/2006 9:00:00 AM"

I need SUM of minutes from pulled rows ...

x=(hours * 60) + minutes ...

By my research, looks like I have to use cursor here (never used before) ...make the loop then convert every row data and manipulate in loop ...

This is how I think ... if anybody have good example, pleas post ...

If anybody have better idea let me know ...

I have to test this ...
SELECT CONVERT(varchar, SUM(myTime), 108) ...how result will come... in minutes???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 13:58:22

You did not answer my question. Is myTime VARCHAR or DATETIME column?

Peter Larsson
Helsingborg, Sweden

ilimax
Posting Yak Master

164 Posts

Posted - 2006-11-07 : 14:04:27

quote:
Originally posted by Peso

You did not answer my question. Is myTime VARCHAR or DATETIME column?

Peter Larsson
Helsingborg, Sweden

Sorry Peso,

I said .. Format of [myTime] is long time "7/8/2006 9:00:00 AM"

DATETIME

I just tried SELECT CONVERT(varchar, SUM(myTime), 108)

I got the error: "sum aggregate opeation cannot take datetime data tyupe as an argument.."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 14:05:33

[code]declare @test table (dt datetime)

insert @test
select '7/8/2006 2:22:40 AM' union all
select '6/11/2005 1:15:00 AM' union all
select '1/1/2007 2:07:55 PM'

select dt, DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, dt), 0), dt) secs
from @test

SELECT CONVERT(varchar, DATEADD(second, SUM(DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, dt), 0), dt)), 0), 108) hourminsec
FROM @Test[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 14:30:39

Or simply

declare @test table (dt datetime)

insert	@test
select	'7/8/2006 2:22:40 AM' union all
select	'6/11/2005 1:15:00 AM' union all
select	'1/1/2007 2:07:55 PM'

SELECT	SUM(DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, dt), 0), dt)) / 60.0 mins
FROM	@Test

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources