Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculating Duration Using DATETIME Start and End Dates (SQL Spackle) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, January 16, 2014 8:35 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 12:43 AM Points: 37,427, Visits: 34,287

#### Try this...

`Create Function dbo.ElapsedTime(      @StartTime DateTime,      @EndTime DateTime)Returns VarChar(20)AsBegin      Declare            @st DateTime,            @et DateTime,            @NeedOffset bit;                 -- Reality check!      If (@StartTime>@EndTime) Return Null;           Select            -- Get isolated time components for start and end DateTime inputs.            @st='1900-01-01 '+Right(Convert(VarChar,@StartTime,121),12),            @et='1900-01-01 '+Right(Convert(VarChar,@EndTime,121),12),            -- If there is a time inversion set a flag indicating that offsetting is required.            @NeedOffset=Case when @st>@et then 1 else 0 End;       -- The DateTime data-type can represent dates between 1753-01-01 00:00:00.000 and      -- 9999-12-31 23:59:59.998, inclusive. However, DateDiff(ms,...) will throw an Int      -- overflow exception if the differential is close to 25 days, therefore, this method      -- processes the days component separately from the time component.      Return            -- Output the DateTime days differential (0 - 3012153).            Cast            (                  DateDiff(dd,@StartTime,@EndTime)-                  -- Apply day offset, if necessary.                  Case @NeedOffset when 1 then 1 else 0 End                  as VarChar            )+            ':'+            -- Output the DateTime time differential (00:00:00.000 - 23:59:59.999).            -- This is done by replacing both input value's date component with            -- '1900-01-01' before running the appropriate conversion functions.            Right            (                  Convert                  (                        VarChar,                        DateAdd                        (                              ms,                              DateDiff                              (                                    ms,                                    @st,                                    @et                              )+                              -- Handle milliseconds offset, if necessary.                              Case @NeedOffset when 1 then 86400000 else 0 End,                              0                        ),                        121                  ),                  12            );End`

As I said, I'm always up for alternate methods and very much appreciate your time in posting that code. I did try it on the million row test table and it took over 26 seconds to complete where the same code (both executed on my slower 12 year old box) only took a little over 3 . Part of the problem is that it's a scalar function, which will usually mean that, all else being equal, it will run about 7 times slower than direct code or an iSF (proverbial "Inline Scalar Function", which is really "just" an Inline Table Valued Function that returns a scalar value).

While that doesn't seem to be a big difference to many, things like this really start to add up in the face of scalability.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

How to post code problems
How to post performance problems
Post #1531905
 Posted Thursday, January 16, 2014 8:53 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 12:43 AM Points: 37,427, Visits: 34,287
 dwain.c (1/16/2014)I must say, I love the technique Jeff! Very simple, straightforward and clean with good performance. Nothing more straightforward comes immediately to mind (issues noted above aside).Normally I take your word on performance but I in this case I played a bit and found something interesting. Running your 1M row test harness, followed by the below query:` SET STATISTICS TIME ON; SELECT @BitBucket = STUFF(CONVERT(VARCHAR(20),s,114),1,2,DATEDIFF(hh,0,s)) FROM #JBMTest CROSS APPLY (SELECT EndDT-StartDT) a (s); SET STATISTICS TIME OFF;`Generated different speed results on SQL 2008 R2 vs. SQL 2012.SQL 2008 R2 (expected - second result uses CA for intermediate calculation):`(1000000 row(s) affected) SQL Server Execution Times: CPU time = 702 ms, elapsed time = 699 ms. SQL Server Execution Times: CPU time = 717 ms, elapsed time = 707 ms.`SQL 2012 (unexpected):`(1000000 row(s) affected) SQL Server Execution Times: CPU time = 749 ms, elapsed time = 738 ms. SQL Server Execution Times: CPU time = 671 ms, elapsed time = 674 ms.`Improvements in the optimizer I reckon.Thanks for the feedback, Dwain. Like I said in the article, most of the time was spent on display and the actual time expended for the calculation was right around 717ms using the "@BitBucket" method for taking the display out of the picture. Your code certainly backs that claim up.As for the 2012 code, I'm actually disappointed to see those results and wouldn't call it an "improvement" to the optimizer at all. It seems to indicate that you need more complex code to get the same performance out of what used to be simpler code. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1531908
 Posted Thursday, January 16, 2014 8:57 PM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 11:01 PM Points: 3,933, Visits: 6,171
 Jeff Moden (1/16/2014)[quote]dwain.c (1/16/2014)As for the 2012 code, I'm actually disappointed to see those results and wouldn't call it an "improvement" to the optimizer at all. It seems to indicate that you need more complex code to get the same performance out of what used to be simpler code.I can see that perspective. It does open interesting possibilities though. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1531910
 Posted Thursday, January 16, 2014 9:04 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 12:43 AM Points: 37,427, Visits: 34,287
 rho_pooka (1/16/2014)Thank you for article, enjoyed the read!Thank you very much for the feedback. I appreciate it. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1531911
 Posted Thursday, January 16, 2014 9:07 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 12:43 AM Points: 37,427, Visits: 34,287
 Thomas Abraham (1/16/2014)I meant to post this earlier, but got caught up in some of the tangent. Thanks to Jeff for providing this spackle. Great job, as always.Thanks for the feedback and for the cover in my absence. Great job, Thomasc and thank you very much for the help. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1531912
 Posted Thursday, January 16, 2014 9:40 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 12:43 AM Points: 37,427, Visits: 34,287
 Since there was some interest in correctly calculating the duration even if the start and end dates were reversed, here's a function that straightens that out. Personally, I'd much rather ensure that the data in the table were actually correct or that an application was passing the dates correctly but this "iSF" function would do just fine (it's only a little slower than the original code) if such things were not possible.` CREATE FUNCTION dbo.Duration/********************************************************************************************************************** Purpose: Given a start and end date as a DATETIME, regardless of the temporal order, calculate the duration between the lowest datetime and the highest and format the output as HHH:MI:SS.nnn WHERE "HHH" is any number of hours up to the maximum maximum difference that DATEDIFF and CONVERT can handle (MAX date of 9999-01-01 23:59:59.000 or 71,003,135 hours or 3ms less than 10,000 years). Obviously, if either value is NULL, a NULL will be returned. Usage Example: SELECT ca.FormattedDuration FROM #JBMTest tt CROSS APPLY dbo.Duration(tt.StartDT, tt.EndDT) ca Ref: http://www.sqlservercentral.com/articles/T-SQL/103343/ Revision History: Rev 00 - 16 Jan 2014 - Jeff Moden - Initial creation and unit test.**********************************************************************************************************************/ ( @StartDT DATETIME ,@EndDt DATETIME )RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH cteSortDates AS (--==== Sort the 2 dates with a "short circuit" in the CASEs for the expected order. SELECT StartDT = CASE WHEN @StartDT <= @EndDT THEN @StartDT ELSE @EndDT END ,EndDT = CASE WHEN @StartDT <= @EndDT THEN @EndDT ELSE @StartDT END ) SELECT FormattedDuration = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT)) FROM cteSortDates;` --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1531916
 Posted Thursday, January 16, 2014 10:07 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 12:43 AM Points: 37,427, Visits: 34,287
 Crud. I just noticed a limitation and I need to post another correction to the article. I tested for all dates from 1900-01-01 00:00:00.000 through 9999-12-31 23:59:59.997. If you have just 3ms more time span, the code fails with an overflow. This is apparently because CONVERT has a limit the same as the MAX SQL DateTime of 9999-12-31-23:59:59.997 (and why wouldn't it?). Even just 3ms more, and you get an overflow error. That means this method has a limit of 3ms less than 10,000 years. Still, that's better than 148 years but it's still an error in the article that needs to be corrected, which I'll do in the morning.Here's the code that demonstrates the problem above...`--==== This works.SELECT CONVERT(VARCHAR(20),CAST('9999-12-31 23:59:59.997' AS DATETIME)-CAST('1900-01-01 00:00:00.000' AS DATETIME),114);--==== Adding just 3ms more duration causes CONVERT to fail.SELECT CONVERT(VARCHAR(20),CAST('9999-12-31 23:59:59.997' AS DATETIME)-CAST('1899-12-31 23:59:59.997' AS DATETIME),114);` --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1531918
 Posted Thursday, January 16, 2014 10:43 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, April 21, 2015 7:08 PM Points: 190, Visits: 448
 Thanks Jeff for the performance analysis. The two main reasons that it is slower are:1) Its coded as a scalar function2) It also handles days, i.e., it outputs a string like DDD:HH:MM:SS.mmmI've altered things a bit and here are the results from 1,000,000 test rows:Your code outputting [HHH:MM:SS.mmm]: `STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))`CPU time = 1202 ms, elapsed time = 1182 ms.My refactored code outputting [DDD:HH:MM:SS.mmm]: `-- Emit the days number...Cast(DateDiff(dd,StartDT,EndDT)-- ...subtracting a day when the times are inverted.-Case when Convert(VarChar,StartDT,114)>Convert(VarChar,EndDT,114) then 1 else 0 End as VarChar)-- Emit the separator and time components.+':'+Convert(VarChar,EndDT-StartDT,114)`CPU time = 2527 ms, elapsed time = 2381 ms.I'm willing to eat the extra time to get the elapsed time in DDD:HH:MM:SS.mmm format, especially since we only use this to output elapsed time between DML in procedures during development.I didn't realize that you could perform simple math (+, -) on DateTime types! Learn something new everyday. Thanks, you rock! PeteKI have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #1531927
 Posted Friday, January 17, 2014 3:10 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, May 20, 2015 3:15 PM Points: 7, Visits: 218
 Fantastically simple!As for how to get the duration in YMD, here's what I came up with. I tried to code it the way I would process it in my head (scary, I know!). My approach is that we humans would intuitively account for the differences in the days of the month and leap years by subtracting the dates in part.1) first the whole years difference2) then the number of whole months left over from the whole years difference3) then the number of days left over from the whole months difference. Thus, the difference in days is dependent on the number of days in the month from the start date.Anyway, for what it's worth here's the SQL I came up with.DECLARE @StartTime datetime, @EndTime datetime;SET @StartTime = '1964-07-24';SET @EndTime = '2014-01-17';SELECT CONVERT(varchar(4), CASE WHEN DATEPART(MONTH,@EndTime) < DATEPART(MONTH,@StartTime) -- If month of @EndTime is < the month of @StartTime, then there is a fraction of a year between the two dates, so subtract 1 from the year difference THEN DATEPART(YEAR,@EndTime) - DATEPART(YEAR,@StartTime) - 1 -- If the month of @EndTime is >= the month of @StartTime, then difference the years and take one away if there is only a fraction of a month between the dates (i.e. the day of @EndTime < the day of @StartTime) ELSE DATEPART(YEAR,@EndTime) - DATEPART(YEAR,@StartTime) - CASE WHEN DATEPART(DAY,@EndTime) < DATEPART(DAY,@StartTime) THEN 1 ELSE 0 END END ) + 'Y ' + CONVERT(varchar(4), CASE WHEN DATEPART(MONTH,@EndTime) <= DATEPART(MONTH,@StartTime) -- If the month of @EndTime <= the month of @StartTime, then get the difference in months by adding 12 (i.e. one year) to @EndTime THEN DATEPART(MONTH,@EndTime) + 12 - DATEPART(MONTH,@StartTime) -- Otherwise, simply subtract the month of @StartTime from the month of @EndTime ELSE DATEPART(MONTH,@EndTime) - DATEPART(MONTH,@StartTime) END -- If month of @EndTime is < month of @StartTime, then there is a fraction of a year between the two dates, so subtract 1 from the month difference - CASE WHEN DATEPART(DAY,@EndTime) < DATEPART(DAY,@StartTime) THEN 1 ELSE 0 END -- If the months are the same and the day of @EndTime is >= the day of @StartTime, then subtract 12 to prevent 12M from occurring in the output -- The logic above for the year difference accounts for the full year - CASE WHEN DATEPART(MONTH,@EndTime) = DATEPART(MONTH,@StartTime) AND DATEPART(DAY,@EndTime) >= DATEPART(DAY,@StartTime) THEN 12 ELSE 0 END ) + 'M ' + CONVERT(varchar(4), CASE WHEN DATEPART(DAY,@EndTime) < DATEPART(DAY,@StartTime) -- If the day of @EndTime is < the day of @StartTime, then subtract the difference between @StartTime - @EndTime from the number of days in the month of @StartTime -- This give the intuitive difference in days based on the @StartTime after getting the difference in years and months above. THEN DATEDIFF(DAY, DATEADD(DAY, 1 - DAY(@StartTime), @StartTime), DATEADD(MONTH, 1, DATEADD(DAY, 1 - DAY(@StartTime), @StartTime))) -- Number of days in the month of @StartTime - (DATEPART(DAY,@StartTime) - DATEPART(DAY,@EndTime)) -- Otherwise, simply subtract the day of @StartTime from the day of @EndTime ELSE DATEPART(DAY,@EndTime) - DATEPART(DAY,@StartTime) END ) + 'D ' AS 'Time Period' -- RTWBe curious!
Post #1532306
 Posted Friday, January 17, 2014 4:43 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, April 21, 2015 7:08 PM Points: 190, Visits: 448
Another bit of SQL weirdness with the DateTime datatype.

#### Setup...

According to BOL a DateTime variable will hold dates ranging from 1753-01-01 through 9999-12-31.
By not also specifying the time component they judiciously avoided a few nasty questions. Here are some examples that illuminate this issue.

This example uses implicit casting to convert a date/time string into a DateTime data type; using actual DateTime values will not change the outcome:

`Declare   @MinDT DateTime,   @MaxDT DateTime;Select   @MinDT ='1753-01-01 00:00:00.000',   @MaxDT ='9999-12-31 23:59:59.998'Select   @MinDT,   @MaxDT;`

The final SELECT statement will output:

1753-01-01 00:00:00.000 | 9999-12-31 23:59:59.997

Notice the millisecond difference between the assignment of @MaxDT and its value in the row set output?
Once again just so you won't think implicit casting has anything to do with this:

`Declare   @MinDT DateTime,   @MaxDT DateTime;Select   @MinDT ='1753-01-01 00:00:00.000',   @MaxDT ='9999-12-31 23:59:59.998'Select   @MinDT,   @MaxDT;-- @MaxDT is now a valid DateTime value.Set @MaxDT=DateAdd(ms,1,@MaxDT);Select   @MinDT,   @MaxDT;`

Both SELECT statement output the same results:

1753-01-01 00:00:00.000 | 9999-12-31 23:59:59.997

1753-01-01 00:00:00.000 | 9999-12-31 23:59:59.997

Since the accuracy of a DateTime data type is 0.00333 then the highest actual millisecond value would be 1.0-0.00333=0.99667 or 0.997 after rounding.
Explanation:
1.0-0.998=0.002, 0.998-0.9967=0.0013; hence 0.998 is closer to 0.9967 than it is to 1.0 so the rounded value is 0.997
Had the rounding gone the other way the assignment to @MaxDT would throw an "out-of-range datetime value" exception.

This leads us to our next two examples:

`Declare   @MinDT DateTime,   @MaxDT DateTime;Select   @MinDT ='1753-01-01 00:00:00.000',   @MaxDT ='9999-12-31 23:59:59.999'`

`Declare   @MinDT DateTime,   @MaxDT DateTime;Select   @MinDT ='1753-01-01 00:00:00.000',   @MaxDT ='9999-12-31 23:59:59.997'Set @MaxDT=DateAdd(ms,2,@MaxDT)`

Now it should be more readily understood why both of these examples throw "out-of-range datetime value" exceptions even though the apparent values appear to be in range.

#### Promised weirdness...

`Declare   @MinDT DateTime,   @MaxDT DateTime;Select   @MinDT ='1753-01-01 00:00:00.000',   @MaxDT ='9999-12-31 23:59:59.997'Select   @MinDT,   @MaxDT,   @MaxDT-@MinDT;`

This code throws "out-of-range datetime value"! I don't know about you but this messed with my head. I would have thought that I would get the lowest DateTime value back, i.e., 1753-01-01 00:00:00.000. In fact, with a little testing I found that the following will work!

`Declare   @MinDT DateTime,   @MaxDT DateTime;Select   @MinDT ='1753-01-01 00:00:00.000',   @MaxDT ='9852-12-31 23:59:59.997'Select   @MinDT,   @MaxDT,   @MaxDT-@MinDT;`

Hmm, looks like we really don't have years 1753 - 9999 as a consistently valid range for all DateTime operations?!? In fact there is a 147 year discrepancy! A little more testing reveals:

`Select GetDate()-GetDate();`

Which returns: 1900-01-01 00:00:00.000 instead of 1753-01-01 00:00:00.000! Now I see what's going on here. The 147 years is the difference between 1900 and 1753! This is why some programmer's DateTime code will overflow sooner/later than other programmer's DateTime code; it depends on which functions you use to calculate the date and time differences. However, given that most of us probably won't be programming in Microsoft SQL (at least today's current versions!) in 9999, a 147 year discrepancy is probably not a problem.

Therefore, if ya just can't spare 147 years in your elapsed time functions don't use an algorithm that uses the binary subtract operator ("-") to get the DateTime difference; use DateDiff on DateTime values where the CCYY-MM-DD component has been edited to the same value, such as 1900-01-01 (taking into account any time range inversion).

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #1532336

 Permissions