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

 

[vongrunt]

datediff() returns integer, 60 is also an integer and result got rounded to integer. Try with 60.0 instead of 60

Do you need difference in decimal form or hh:mm:ss?

 

[mrdenny]

Datediff assumes a datatype of INT. You need to convert it to numeric before deviding by 60.

select convert(numeric(8,2), datediff(mi,starttime,endtime))/60 
from Fact_tRouteDetail

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[talenx]

WOW!!, you guys are quick....
I knew it had to be something simple...
vongrunt,mrdenny
Yes both solutions work perfectly.

Though to answer your question vongrunt having the variance in hh:mm:ss would be ideal.

Thank you both again very much, you have saved me quite a few hours possibly days of head banging

Thanks
Talenx

 

[vongrunt]

Try this sample code in QA, it will give you some ideas:

declare @date1 datetime, @date2 datetime
set @date1 = '2004-11-19 12:30:25'
set @date2 = getdate()

select @date1, @date2,
	datediff(ss, @date1, @date2) as diff_sec_num,
	dateadd( ss, datediff(ss, @date1, @date2), 0 ) as diff_date,
	convert(varchar(8), dateadd( ss, datediff(ss, @date1, @date2), 0 ), 108) as diff_str

 

[ESquared]

SELECT Elapsed=Convert(varchar(8),endtime-starttime,108)

This only works up to 24 hours, though, then it starts counting again at zero. Do you want one that can handle larger intervals?



-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[talenx]

vongrunt,
Again I am humbled by your knowledge... That work perfect I was able to create a stored procedure to store the time variance or perform a simple select.
Thank you again...

Talenx

 

[talenx]

ESquared ,
Thank you for you suggestion but I don't think I’m going to run into a situation where I’m going to have time variances running over 24 hours, though if that did happen what would be a good choice of logic?

It’s always good to plan ahead... Just in case.

Thanks
Talenx

 

[ESquared]

Do you want it to say "1d 06:24:36" or "30:24:36"

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[talenx]

ESquared.
thanks for the quick reply,

more then likely i would want to see "1d 06:24:36" that is if the "1d" is a concated string that could be changed to say n day(s) + 06:24:36

Thanks
Talenx

 

[ESquared]

Easy, then!

SELECT
   Elapsed=
      CASE WHEN endtime-starttime >= 1 THEN Convert(varchar(6), Convert(int,endtime-starttime))+'d+' ELSE '' END 
      + Convert(varchar(8),endtime-starttime,108)

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[talenx]

Perfect!!!!


I see that you use this in a CASE statement if the time variance is greater then 24 hours...
This makes the implementation very smooth

Thank you ALL for all of you assistance

Thanks
Talenx

 

[ESquared]

CASE WHEN endtime >= starttime + 1 ...

Might be faster in terms of indexes because endtime is by itself on one side of the operator... just a thought.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[talenx]

Hey guys,
Well the solution that was provided was great!!! But unfortunately I ran into an issue when performing calculation against the already converted time.

Example:
Lets say we have four sets of time

start time - end time = time difference

start time2 - end time2 = time difference2

** this is where I run into the issue **
time difference + time difference = total time difference

This what I have so far:

SELECT TimeDifference=Convert(varchar(8),EndTime-StartTime,108),StemTimeDifference=Convert(varchar(8),LastStopTime-FirstStartTime,108) from Fact_tRouteDetail

-- I have attempted to covert the varchar back to a numeric but with no avail.

Any Thoughts?

Thank you ALL once again for your assistance.

talenx

 

[ESquared]

Don't convert to varchar before adding.

convert(varchar(8), starttime - endtime + starttime2 - endtime2) = total time difference.

If you are storing the results of your query in a table for later processing, don't do the convert to varchar until the final step... store as datetime until the query which displays it all.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[talenx]

Perfect...
I knew it had to be simple.



thank you very much.
talenx

 

[ESquared]

You might want to explicitly choose a style parameter for your conversions so you're never surprised if someone changes SET DATEFORMAT on you accidentally.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[ghdavis]

Those solutions are very creative and well thought out, however, I have a date problem that no one so far has been able to give me a solution to. I am an experienced programmer and pretty familiar with handling dates but I can't figure this one out. Here it is.

A company has 8 hours , 8AM - 5PM Monday Through Friday to fix a problem. If the fix takes more then the 8 hours, the company is fined a lot of money. A trouble ticket is created, as an example on Saturday at 3PM - it is resolved on Monday at 3PM. I know how to calculate the hours & minutes by subtracting the create time from the resolved time, that’s easy. What I can't figure out is how to determine if the 8 hours was exceeded when the start time and end time spans a weekend or when the start time is at 6AM and the stop time is at 9PM. I don't know how to eliminate the hours that occur before the Allowed 8AM and finished after the allowed 5PM and then to make it interesting add the weekend in. HELP????

Many, many thanks,
George

 

[ESquared]

ghdavis, I know exactly how to solve this problem. But would you ask it in a new thread? Please post your question and give us a link, here.

 

[donutman]

Well, e², I'm tired of waiting and I want to see your solution. Here's mine:

[Blue]CREATE[/Blue] [Blue]FUNCTION[/Blue] dbo.TimeDiff [Gray]([/Gray]@StartDate [Blue]AS[/Blue] [Blue]datetime[/Blue][Gray],[/Gray] @EndDate [Blue]AS[/Blue] [Blue]datetime[/Blue][Gray])[/Gray]
   returns [Blue]int[/Blue]
[Blue]AS[/Blue]
[Blue]BEGIN[/Blue]
   [Blue]DECLARE[/Blue] @NewEnd [Blue]datetime[/Blue]
   [Blue]DECLARE[/Blue] @NewStart [Blue]datetime[/Blue]
   [Blue]SET[/Blue] @NewEnd[Gray]=[/Gray] [Blue]CASE[/Blue] [Gray]([/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]hour[Gray],[/Gray][Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray]0[Gray],[/Gray]@EndDate[Gray])[/Gray][Gray],[/Gray]0[Gray])[/Gray][Gray],[/Gray]@EndDate[Gray])[/Gray][Gray]+[/Gray]1[Gray])[/Gray]/9
             [Blue]WHEN[/Blue] 0 [Blue]THEN[/Blue] [red]'12/31/1899 17:00'[/red]
             [Blue]WHEN[/Blue] 1 [Blue]THEN[/Blue] @EndDate[Gray]-[/Gray][Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray]0[Gray],[/Gray]@EndDate[Gray])[/Gray][Gray],[/Gray]0[Gray])[/Gray]
             [Blue]WHEN[/Blue] 2 [Blue]THEN[/Blue] [red]'1/1/1900 17:00'[/red] [Blue]END[/Blue]
          [Gray]+[/Gray] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray]0[Gray],[/Gray]@EndDate[Gray])[/Gray][Gray],[/Gray]0[Gray])[/Gray]
   [Blue]SET[/Blue] @NewEnd[Gray]=[/Gray][Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]hour[Gray],[/Gray][Blue]CASE[/Blue] [Gray]([/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]hour[Gray],[/Gray][red]'1/5/1900 17:00'[/red][Gray],[/Gray]@NewEnd[Gray])[/Gray] [Gray]%[/Gray] 168[Gray])[/Gray]/63
              [Blue]WHEN[/Blue] 0 [Blue]THEN[/Blue] [Gray]-[/Gray][Gray]([/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]hour[Gray],[/Gray][red]'1/5/1900 17:00'[/red][Gray],[/Gray]@NewEnd[Gray])[/Gray] [Gray]%[/Gray] 168[Gray])[/Gray]
                [Blue]ELSE[/Blue] 0 [Blue]END[/Blue][Gray],[/Gray]@NewEnd[Gray])[/Gray]
   [Blue]SET[/Blue] @NewEnd[Gray]=[/Gray][Blue]CASE[/Blue] [Gray]([/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]hour[Gray],[/Gray][red]'1/5/1900 17:00'[/red][Gray],[/Gray]@NewEnd[Gray])[/Gray] [Gray]%[/Gray] 168[Gray])[/Gray]/63
                                   [Blue]WHEN[/Blue] 0 [Blue]THEN[/Blue] [Gray]([/Gray]7[Gray]+[/Gray][Fuchsia]DatePart[/Fuchsia][Gray]([/Gray]weekday[Gray],[/Gray][red]'1/5/1900'[/red][Gray])[/Gray][Gray]-[/Gray][Fuchsia]DatePart[/Fuchsia][Gray]([/Gray]weekday[Gray],[/Gray]@NewEnd[Gray])[/Gray][Gray])[/Gray][Gray]%[/Gray]7[Gray]+[/Gray]
                                                        [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray]0[Gray],[/Gray]@NewEnd[Gray])[/Gray][Gray],[/Gray][red]'1/1/1900 17:00'[/red][Gray])[/Gray]
                                   [Blue]ELSE[/Blue] @NewEnd [Blue]END[/Blue]
   [Blue]SET[/Blue] @NewStart[Gray]=[/Gray] [Blue]CASE[/Blue] [Gray]([/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]hour[Gray],[/Gray][Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray]0[Gray],[/Gray]@StartDate[Gray])[/Gray][Gray],[/Gray]0[Gray])[/Gray][Gray],[/Gray]@StartDate[Gray])[/Gray][Gray]+[/Gray]1[Gray])[/Gray]/9
             [Blue]WHEN[/Blue] 0 [Blue]THEN[/Blue] [red]'1/1/1900 8:00'[/red]
             [Blue]WHEN[/Blue] 1 [Blue]THEN[/Blue] @StartDate[Gray]-[/Gray][Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray]0[Gray],[/Gray]@StartDate[Gray])[/Gray][Gray],[/Gray]0[Gray])[/Gray]
             [Blue]WHEN[/Blue] 2 [Blue]THEN[/Blue] [red]'1/2/1900 8:00'[/red] [Blue]END[/Blue]
        [Gray]+[/Gray] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray]0[Gray],[/Gray]@StartDate[Gray])[/Gray][Gray],[/Gray]0[Gray])[/Gray]
   [Blue]SET[/Blue] @NewStart[Gray]=[/Gray][Blue]CASE[/Blue] [Gray]([/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]hour[Gray],[/Gray][red]'1/5/1900 17:00'[/red][Gray],[/Gray]@NewStart[Gray])[/Gray] [Gray]%[/Gray] 168[Gray])[/Gray]/54
                                   [Blue]WHEN[/Blue] 0 [Blue]THEN[/Blue] [Gray]([/Gray]7[Gray]+[/Gray][Fuchsia]DatePart[/Fuchsia][Gray]([/Gray]weekday[Gray],[/Gray]0[Gray])[/Gray][Gray]-[/Gray][Fuchsia]DatePart[/Fuchsia][Gray]([/Gray]weekday[Gray],[/Gray]@NewStart[Gray])[/Gray][Gray])[/Gray][Gray]%[/Gray]7[Gray]+[/Gray]
                                                        [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray]0[Gray],[/Gray]@NewStart[Gray])[/Gray][Gray],[/Gray][red]'1/1/1900 8:00'[/red][Gray])[/Gray]
                                   [Blue]ELSE[/Blue] @NewStart [Blue]END[/Blue]
   [Blue]RETURN[/Blue] [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]minute[Gray],[/Gray] @NewStart[Gray],[/Gray] @NewEnd[Gray])[/Gray]
[Blue]END[/Blue]

I think it works independent of DATEFIRST setting. Sorry about the format, try and make the page wide enough. Reformatting to a smaller width makes it hard to see what's going on.
-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]