Help with date time calculations please 2

[Webkins]

Good morning. I am using MSSql 2000 and a table with 2 date/time columns. One is called "Issued" and the other is "Resolved" I would like to calculate the difference in time between the two in Hours, Minutes and Seconds for a query report and I have no idea where to start as this will be my first calculation of this nature.

Issued Resolved
4/4/2009 7:27:22 AM 4/4/2009 6:14:42 PM

Thank you for your help with this task.

 

[hmckillop]

try

declare @Issued datetime, @Resolved datetime

--two ways

select @Issued = '4/4/2009 7:27:22 AM'
select @Resolved = '4/4/2009 6:14:42 PM'
    
select datepart(hh,  @Resolved-@Issued ) as Hours,
		datepart(mi,  @Resolved-@Issued) as Minutes ,
		datepart(ss,  @Resolved-@Issued) as Seconds

"I'm living so far beyond my income that we may almost be said to be living apart

 

[bborissov]

DECLARE @StartTime datetime, @endTime datetime

SET @StartTime = '20090404 07:27:22'
SET @EndTime   = '20090404 18:14:42'

SELECT DATEDIFF(ss,@StartTime, @EndTime)/3600  as Hours,
       (DATEDIFF(ss,@StartTime, @EndTime) - (DATEDIFF(ss,@StartTime, @EndTime)/3600*3600))/60  as Minutes,
       DATEDIFF(ss,@StartTime, @EndTime) - (DATEDIFF(ss,@StartTime, @EndTime)/60*60)  as Secs

Not properly tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Webkins]

bborissov, this works but I don't understand the use of your SET statements. The data seems to be fixed data. I need to select the data from my table and then calculate using that. I am sorry, I don't get it.

Database name: CoilSpec
Table name: HelpData
Column name Issued, Date/Time
Column name Resolved, Date/Time

 

[bborissov]

For testing )
Just change @StartTime to your Issued field name and @EndTime to Resolved field name.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Webkins]

Ahhhhhhh, I get it now. Thanks a million. Have a star.