DateTime Torture - 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)

DateTime Torture

Author

Topic

hansalas
Starting Member

14 Posts

Posted - 2006-08-22 : 04:44:02

Hi, having some problems with the datetime functionality...
Here goes,

1st Problem:

select id,endtime-starttime,numofpages,
(3600*numofpages)
(                  
(3600*datediff(hour,starttime,endtime))+
(60*datediff(minute,starttime,endtime))+
(datediff(second,starttime,endtime))
)
from table1

I wanna print out id,Time taken,num of pages,numofpages/hour
If U r wondering why the numerator *3600 is cos,i multiply the numerator and denominator by 3600..dats all..

Problem:
numofpages/hour is occasionally inaccurate..Is there a way i can rewrite the numofpages/hour in a more optimised way..

2nd Problem:

select id, sum(endtime-starttime)
from table
group by id

Problem:
I wanna find the total time taken per user..The problem is that you cant do DateTime component like that...
Here is one solution I found in this forums..
Convert(varchar(10),sum(DatePart(hh,endtime-starttime)))+':'+Convert(varchar(10),sum(DatePart(mi,endtime-starttime)))+':'+Convert(varchar(10),sum(DatePart(ss,endtime-starttime))),
But this solution , jez adds all the seconds together and gives out for example 0:0:112..while the actuall answer 0:1:52

Thanks in advance , guys...

Query writing is hellaluva stressful job, man...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 05:01:32

Do you have some sample data?
In your first problem, why do you add hours and minutes? DATEDIFF(second... takes care of ALL seconds between the two times.

Your first problem would be solved with this

select	id,
	DATEDIFF(seconds, starttime, endtime) / 3600.0,
	numofpages,
	numofpages * 3600.0 / DATEDIFF(seconds, starttime, endtime)
from	table1

and your second problem would be solved with this

select		id,
		sum(DATEDIFF(second, starttime, endtime)) / 3600.0
from		table1
group by	id

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-22 : 05:07:50

1. does this work at all?
try it with starttime = '20050101' endtime = '20050102', nomofpages = 50

I would have thought something like
3600.0 * numofpages / datediff(ss,starttime,endtime)

2.
how about
select convert(datetime,sum(convert(float,endtime)-convert(float,starttime))) from #a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hansalas
Starting Member

14 Posts

Posted - 2006-08-22 : 05:48:13

Hi,
Qn 1. Peso ur answer was sweet...Solved my problem
Qn 2.
Peso , ur answer still onli gives the answer is second but not in datetime format
nr, Ur answer gives the correct answer but it is in this format...

convert(datetime,sum(convert(float,endtime)-convert(float,starttime)))

Gives 1900-01-01 00:01:53.223 but i don wan the year month day stuff..
Just this 00:01:53 will do...
I tried to substring it..but it says cannot substring datetime...
Any Idea...
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 05:52:14

This will solve problem 2, until sum of seconds is less than 86400.

select		id,
		convert(varchar, DATEADD(second, sum(DATEDIFF(second, starttime, endtime)), 0), 108)
from		table1
group by	id

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources