Calculating Time Variance (HELP!!) 3

[talenx]

Hello all,
This is more then likely very simple and I’m just overlooking it. But I have been racking my head on this for a couple of days now, and I haven't been able to come up with a viable solution.
I have a start time and an end time with in the same record.
Here is the dataset
ID starttime endtime
------------------------------------------------------ ------------------------------------------------------
1 2004-11-18 04:33:00.000 2004-11-18 14:33:00.000
2 2004-11-18 04:00:00.000 2004-11-18 14:00:00.000
3 2004-11-18 04:00:00.000 2004-11-18 14:00:00.000
4 2004-11-17 04:23:00.000 2004-11-17 14:43:00.000
5 2004-11-16 07:45:00.000 2004-11-16 16:37:00.000
6 2004-11-16 05:56:00.000 2004-11-16 15:45:00.000
---
End dataset
I need to be able to calculate the time difference between the start time and the end time

Example
Start time = 5:30 am
End time = 3:00 pm
Difference = 9:30 hours

I attempted to use the DATEDIFF function but it would always round up to the nearest hour, never showing the minutes

select datediff(mi,starttime,endtime)/60 from Fact_tRouteDetail

--RESULT SET 
ID	Variance
1	10
2	10
3	10
4	10
5	8
6	9

Any thoughts

Thanks
Talenx

 

[ESquared]

Thanks for the research, gmmastros. It's definitely best to always specify length.

 

[fireant]

All your feedback is enlightening guys and I'm learning a lot!

Karl, I treated your comment as a challenge so I modified it somewhat and here's what I ended up with that's working:

[blue]SELECT sequence AS[/blue] Ticket_No, hd_ccdt01 [blue]AS[/blue] Start_time, hd_ccdt02 [blue]AS[/blue] End_time,
 ([blue]SELECT[/blue] [fuchsia]Convert[/fuchsia]([blue]varchar[/blue](5),[fuchsia]datediff[/fuchsia](ss,hd_ccdt01,hd_ccdt02) / 86400) + [red]'d '[/red]
  + [fuchsia]Convert[/fuchsia]([blue]varchar[/blue](30),[fuchsia]DateAdd[/fuchsia](ss,[fuchsia]datediff[/fuchsia](ss,hd_ccdt01,hd_ccdt02),0),108)) [blue]AS[/blue] Duration
[blue]FROM[/blue] _smdba_._telmaste_

I wanted to do this, though. It parsed ok, but when I executed the command, it said [red]tsec[/red] was an invalid column name:

[blue]SELECT sequence AS[/blue] Ticket_No, hd_ccdt01 [blue]AS[/blue] Start_time, hd_ccdt02 [blue]AS[/blue] End_time,
([blue]SELECT[/blue] [fuchsia]datediff[/fuchsia](ss,hd_ccdt01,hd_ccdt02) [blue]as[/blue] tsec,
 ([blue]SELECT[/blue] [fuchsia]Convert[/fuchsia]([blue]varchar[/blue](5), tsec / 86400) + [red]'d '[/red]
  + [fuchsia]Convert[/fuchsia]([blue]varchar[/blue](30),[fuchsia]DateAdd[/fuchsia](ss,tsec,0),108)) [blue]AS[/blue] Duration)
[blue]FROM[/blue] _smdba_._telmaste_

Louie C.

 

[fireant]

Sorry... posted too soon as well... This is the most concise set of statements I can come up with.

[blue]SELECT sequence AS[/blue] Ticket_No, hd_ccdt01 [blue]AS[/blue] Start_time, hd_ccdt02 [blue]AS[/blue] End_time,
([blue]SELECT[/blue] [fuchsia]Convert[/fuchsia]([blue]varchar[/blue](5), [fuchsia]datediff[/fuchsia](ss,hd_ccdt01,hd_ccdt02) / 86400) + [red]'d '[/red]
  + [fuchsia]Convert[/fuchsia]([blue]varchar[/blue](30),[fuchsia]DateAdd[/fuchsia](ss,[fuchsia]datediff[/fuchsia](ss,hd_ccdt01,hd_ccdt02),0),108)) [blue]AS[/blue] Duration)
[blue]FROM[/blue] _smdba_._telmaste_

Anything else, I'm stumped...


Louie C.

 

[donutman]

When you put a Select within the Select list it makes it look like you're computing a scaler value or trying to use a derived table. In this case you don't need either of those two.
ESquared has also demonstrated (in a different thread) that to compute durations accurately (down to the second), that it is imperative that you use the DateDiff(ss,Date1,Date2) in a slightly different format: DateDiff(ss,0,Date2-Date1). You may want to study the impact of that.

[Blue]SELECT[/Blue] [sequence] [Blue]AS[/Blue] Ticket_No[Gray],[/Gray] 
       hd_ccdt01 [Blue]AS[/Blue] Start_time[Gray],[/Gray] 
       hd_ccdt02 [Blue]AS[/Blue] End_time[Gray],[/Gray]
       [Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Blue]varchar[/Blue][Gray]([/Gray]5[Gray])[/Gray][Gray],[/Gray] [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]ss[Gray],[/Gray]hd_ccdt01[Gray],[/Gray]hd_ccdt02[Gray])[/Gray] / 86400[Gray])[/Gray] [Gray]+[/Gray] [red]'d '[/red] [Gray]+[/Gray]
       [Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Blue]varchar[/Blue][Gray]([/Gray]8[Gray])[/Gray][Gray],[/Gray][Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]ss[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]ss[Gray],[/Gray]0[Gray],[/Gray]hd_ccdt02[Gray]-[/Gray]hd_ccdt01[Gray])[/Gray][Gray],[/Gray]0[Gray])[/Gray][Gray],[/Gray]108[Gray])[/Gray] [Blue]AS[/Blue] Duration
   [Blue]FROM[/Blue] _smdba_._telmaste_

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

DECLARE
	@StartTime datetime,
	@EndTime datetime
SET @StartTime = '3/1/2005 11:59:59.997'
SET @EndTime = '3/1/2005 12:00:00.000'

SELECT
	ms = DateDiff(ms, @StartTime, @EndTime),
	s =  DateDiff(s, @StartTime, @EndTime),
	s2 = DateDiff(s, 0, @EndTime - @StartTime)

--Result set
ms s s2
3  1 0

So, because @StartTime and @EndTime are on opposite sides of a whole-second boundary, with the first method it counts as one second difference, even though they are only three milliseconds apart. The subtraction method is likely to most often give the kind of results you'd expect, which is 0 seconds (the actual difference between the two times is less than one full second).

 

[fireant]

I actually just need up to the minute. Milliseconds don't matter for my purpose. I would have just converted it to hh:mm for reporting purposes, and since hh is only up to 24hrs, I had to use the day as well for other people to understand.

Thanks, all you guys for your help! I was able to apply it to my trouble-ticketing system.

Louie C.