Jeff Moden (1/24/2015)
RonKyle (1/24/2015)
What are the issues with DATETIME2 and DATEDIFF?Simple. Given a single row that contains StartDate and EndDate columns as DATETIME2 columns, write the code to calculate the duration between those two columns and display it in the HHHHHHHH:MI:SS.mil format. Once that's done, write the code to aggregate that duration that StartDate and EndDate represent for the entire table as a SUM. Post the code and then I'll show you how simple it can really be and how it avoids overflows.
That's an interesting question. I worked on it a bit and found an anomaly. Consider this query:
select datediff(microsecond, cast('00:00:00.1234567' as datetime2(7)), cast('00:00:00.7654321' as datetime2(7))), 7654321 - 1234567
I expected that datediff would return the same result as the simple subtraction. WRONG! I got:
6419766419754
So...what's going on here?
Gerald Britton, Pluralsight courses