help please

[rycen116]

guys ive been lurking every forums on the net and i cant seem to find the solution on my project, ok here it goes

i have a two field and i would like to subtract this two fields to get the Downtime

DateStarted
DateEnded

Here are the Sample Data:

07/21/2008 06:30:25 AM
07/21/2008 07:30:30 AM

now i want to get the difference of these two dates the thing is i also need the complete result the format should be look like these: 01:00:05

i have tried using datediff but in no luck i don't know how to manipulate it to get the hour minute and second. i was able to get the 1:00 but i cant find a way how to get the :05.

now if by chance i would be able to get the difference of the two dates including the seconds i would like to add all of the downtime of the two dates is it possible to put it on a query and to show it on reports?

i would appreciate your help guys. thanks

 

[khawar]

Downloaded the attached sample and check query1

 

[khawar]

Made a little ammendment in the module download the revised file

 

[rycen116]

@khawar wow that was so cool i have tried both files and they are working just fine, thanks a lot , well now khawar i want to add another field and i want to add all of the Time Difference of all records example i have 5 data and i want to get the total Time Difference this time i want to add them, thanks in advance

 

[khawar]

Download the attached file and check query

3-ConvertedToHoursMinutesSeconds

 

[rycen116]

@khawar thank you very much to the sample it was realy cool man now i would be able to continue my project

 

[rycen116]

@khawar im trying to make the 3 queries as 1 but it send me an error is it possible to join the three queries? thanks

 

[khawar]

Ofcourse here is the single query to get the total hours

 

[rycen116]

@khawar thanks for the quick reply but now i need to see this on my report
ex.

DateStarted DateEnded Time Difference
7/21/2008 5:20:23 AM 7/21/2008 7:20:20 AM 01:59:57
7/21/2008 5:30:25 AM 7/21/2008 7:20:20 AM 01:49:55

Total Downtime 03:49:52


i have tried creating the report but it gives me an error please can you tell me what is the problem on what im doing? tnx

 

[rycen116]

@khawar please how can i compute the total downtime on the report
thanks in advanced

 

[khawar]

Uploaded the sample again with total time on report

 

[rycen116]

@khawar man you are a genuis seems like any query i ask you have the answer instantly. well my friend dont get tired on teaching us newbies hehe bye.. thanks man

 

[Lightwave]

Quality....!