Jeff Moden (12/23/2012)
What I'm actually trying to get people to realize is that using JUST the time for this type of stuff (EndTime = StartTime + Duration) is the wrong thing to do unless it's ok to "wrap the clock" after midnight. If you have a duration that exceeds 24 hours starting just before midnight of one day, then you really need to show that the call or whatever duration your trying to portray ended two days later just after midnight.In other words, both StartTime and EndTime must show the date AND the time for both.
Of course. Sorry Jeff, I didn't mean to speak for you.
But of interest, what is the meaning in the physical world of the following addition?
2012-12-21 05:00 + 2012-12-24 07:00
If your answer is that you're adding the number of days between Jan 1, 1900 and Dec 12, 2012 (+ 7 hours) to the date Jan 21, 2012 (at 5AM), I'm with you. I'm just saying it's a kludge (albeit a widely used one). Because MS hasn't been so kind to provide a specific type for a date/time interval.
Personally, my preference is to use the DATETIME to hold the date and an INT (or BIGINT) to hold the minutes, or if need be seconds, to represent the interval. Ignoring for the moment the issues you might encounter using BIGINT.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St