## Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

 Author Message Thomas Abraham SSCrazy Group: General Forum Members Points: 2333 Visits: 2254 Eirikur Eiriksson (1/16/2014)Thank you for the spackle Jeff but it looks like there is a small crack in it as it makes the assumption that the end time is always greater than the start time. This is the reason why I prefer to use double cast, first to varbinary and then to bigint. `SELECT StartDT ,EndDT ,Duration = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT)) ,DurationI = STUFF(CONVERT(VARCHAR(20),StartDT-EndDT,114),1,2,DATEDIFF(hh,0,StartDT-EndDT)) ,cast(cast(StartDT as varbinary(128)) as bigint) As StartTicks ,cast(cast(EndDT as varbinary(128)) as bigint) AS EndTicks ,cast(cast(StartDT as varbinary(128)) as bigint)-cast(cast(EndDT as varbinary(128)) as bigint) as XmY ,cast(cast(EndDT as varbinary(128)) as bigint)-cast(cast(StartDT as varbinary(128)) as bigint) as YmXFROM #JBMTest;`I got:Msg 8115, Level 16, State 2, Line 15Arithmetic overflow error converting expression to data type datetime.when I ran it for date differences greater than about 148 years. Maybe not a problem for some implementations, but something to be aware of. Please don't go. The drones need you. They look up to you.Connect to me on LinkedIn Jeff Moden SSC-Forever Group: General Forum Members Points: 45006 Visits: 39880 Mikael Eriksson SE (1/16/2014)There is one more thing to notice about the first query.You assign the value "2000-02-01 12:34:56.789" to @EndDT but the query returns "2000-01-02 12:34:56.789".Either make sure you are using "SET DATEFORMAT YMD" or use an unambiguous date format for the string literal "2000-02-01T12:34:56.789".Thanks for the heads up, Mikael... it wasn't all that about unambiguous dates (which is whatI used)... it was a phat phinger during transfer of the code to the article submission site. I've submitted a correction that they'll, hopefully, be able to get to right away. --Jeff ModenRBAR 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.Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.Although change is inevitable, change for the better is not.Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC-Forever Group: General Forum Members Points: 45006 Visits: 39880 cready27 (1/16/2014)I'm guessing the @EndDT for the first code example should have been.`@EndDT = '2000-01-02 12:34:56.789'`That gives the same answer as the example.Correct. I've submitted a correction. Apparently, I phat phingered the code during my final submittal to the article submittal software. --Jeff ModenRBAR 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.Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.Although change is inevitable, change for the better is not.Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Dennis Wagner-347763 Valued Member Group: General Forum Members Points: 64 Visits: 227 Calculations are done using a calendar year.For example if person A was born October 16, 1995, and we want to figure out how old he/she was on January 15, 2014, we can easily use DATEDIFF(day, '10/16/95', '1/15/14) to get 6,666 days, but we want the result to be formatted as 18 years, 2 months, 30 days. The years are easy, and the months aren't too bad, but the days calculation is based on December having 31 days, so there are 30 days between December 16, 2013 (18 years and 2 months after 10/16/95) and January 15, 2014. However, if we were calculating from 10/16/95 to March 15, 2014, our answer should be 18 years, 4 months, 27 days since there are only 28 days in February. And yes, you are correct that leap years do prevent an issue and need to be addressed. The issue with doing simple DATEDIFF in SQL is that it does a straight subtraction. From the example above:DATEDIFF(year, '10/16/95', '1/15/14') = 19 (but only 18 full years elapsed)DATEDIFF(month, '10/16/95', '1/15/14')%12 = 3 (but only 2 full months elapsed)Days is a real mess.How about this: DATEDIFF(YEAR, '12/31/13', '1/1/14') = 1! (not the factorial) when only 1 day has elapsedWhen comparing who is older when they achieved their Super Duper Fantastical Grandmaster Status, you have to use number of days because it is not affected by leap years, days in a month, etc.So if person A above was compared to person B who was born December 14, 1995 and achieved this noteworthy status on March 15, 2014, then both of them would be 6,666 days old:DATEDIFF(day, '10/16/95', '1/15/14') = DATEDIFF(day, '12/14/95', '3/15/14') = 6,666However, when formatted as YMD, person A is 18 years, 2 months, 30 days while person B is 18 years, 3 months, 1 day, which would make person A seem younger. My original response states why you can't just fix 30 days in a month or you could end up with x years, 12 months, and 4 days when 364 days elapsed from the previous year anniversary.I always use days when figuring out a "top 10" list, but I always format the answer as YMD so the age is meaningful to the audience. Jeff Moden SSC-Forever Group: General Forum Members Points: 45006 Visits: 39880 I can't spend much more time on the discussions that are unfolding until I get home from work tonight but the folks at SQLServerCentral have posted the correction to the first code window that a couple of you brought up. Thank you for the help there. --Jeff ModenRBAR 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.Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.Although change is inevitable, change for the better is not.Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Eirikur Eiriksson SSCertifiable Group: General Forum Members Points: 6695 Visits: 17690 Thomas Abraham (1/16/2014)Eirikur Eiriksson (1/16/2014)Thank you for the spackle Jeff but it looks like there is a small crack in it as it makes the assumption that the end time is always greater than the start time. This is the reason why I prefer to use double cast, first to varbinary and then to bigint. `SELECT StartDT ,EndDT ,Duration = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT)) ,DurationI = STUFF(CONVERT(VARCHAR(20),StartDT-EndDT,114),1,2,DATEDIFF(hh,0,StartDT-EndDT)) ,cast(cast(StartDT as varbinary(128)) as bigint) As StartTicks ,cast(cast(EndDT as varbinary(128)) as bigint) AS EndTicks ,cast(cast(StartDT as varbinary(128)) as bigint)-cast(cast(EndDT as varbinary(128)) as bigint) as XmY ,cast(cast(EndDT as varbinary(128)) as bigint)-cast(cast(StartDT as varbinary(128)) as bigint) as YmXFROM #JBMTest;`I got:Msg 8115, Level 16, State 2, Line 15Arithmetic overflow error converting expression to data type datetime.when I ran it for date differences greater than about 148 years. Maybe not a problem for some implementations, but something to be aware of.The error would be from either the Duration or DurationI as 148 years are well within the approx. 974904000 year limit of the bigint, given there are 300 ticks in a second. The binary-bigint method works on DATETIME and therefore inherits its limits:`declare @t1 datetime = '9999-12-31 23:59:59.000'declare @t2 datetime = '1753-01-01 00:00:00.000'SELECT cast(cast(@t1 as varbinary(128)) as bigint)-cast(cast(@t2 as varbinary(128)) as bigint)` Thomas Abraham SSCrazy Group: General Forum Members Points: 2333 Visits: 2254 Dennis Wagner-347763 (1/16/2014) The issue with doing simple DATEDIFF in SQL is that it does a straight subtraction. From the example above:DATEDIFF(year, '10/16/95', '1/15/14') = 19 (but only 18 full years elapsed)DATEDIFF(month, '10/16/95', '1/15/14')%12 = 3 (but only 2 full months elapsed)Days is a real mess.How about this: DATEDIFF(YEAR, '12/31/13', '1/1/14') = 1! (not the factorial) when only 1 day has elapsedYes, I do see what you mean. See my comment below for a better suggestion.When comparing who is older when they achieved their Super Duper Fantastical Grandmaster Status, you have to use number of days because it is not affected by leap years, days in a month, etc.So if person A above was compared to person B who was born December 14, 1995 and achieved this noteworthy status on March 15, 2014, then both of them would be 6,666 days old:DATEDIFF(day, '10/16/95', '1/15/14') = DATEDIFF(day, '12/14/95', '3/15/14') = 6,666However, when formatted as YMD, person A is 18 years, 2 months, 30 days while person B is 18 years, 3 months, 1 day, which would make person A seem younger. My original response states why you can't just fix 30 days in a month or you could end up with x years, 12 months, and 4 days when 364 days elapsed from the previous year anniversary.I always use days when figuring out a "top 10" list, but I always format the answer as YMD so the age is meaningful to the audience.I was a bit rushed when I responded earlier, and didn't fully communicate what I had in mind when I said, "Which years in the calendar should we use to calculate this?" The solution seems to be to calculate the days for comparison purposes. Then, add the days to a standard reference date, such as 1900-01-01. THEN do your DATEDIFF calculations. That way your 6,666 days will always work out to the same number of years, months & days, no matter what period of time they actually occurred over. The leaps years and months of variable length will always fall in the same spots, so days added to the reference date will always give a determinate result, which I believe is what you require. Please don't go. The drones need you. They look up to you.Connect to me on LinkedIn Dennis Wagner-347763 Valued Member Group: General Forum Members Points: 64 Visits: 227 The solution seems to be to calculate the days for comparison purposes. Then, add the days to a standard reference date, such as 1900-01-01. THEN do your DATEDIFF calculations. That way your 6,666 days will always work out to the same number of years, months & days, no matter what period of time they actually occurred over. The leaps years and months of variable length will always fall in the same spots, so days added to the reference date will always give a determinate result, which I believe is what you require.Thomas, I like the way you are thinking. My solution was to build a function that would use the elapsed days to do the formatting. While I agree that using a reference table would give a consistent determinate result, the issue would be how do we build that reference table. We can't just use 30 days in a month, or we'll run out of months before we run out of days in a year. If we assume like a calendar year that months 1,3,5,7,8,10, and 12 have 31 days, months 4,6,9, and 11 have 30 days and month 2 has either 28 or 29 days, then we'll get the correct number of days and every month of variable length will fall in the same spot. An example would be the standard fiscal week calendar for time shares where February always has 5 weeks every year. That just seems counterintuitive since only 1/4 of the time does it ever have more than 4.The issue then becomes presentation and acceptance. If I compare someone born on February 15th with a date on March 16th, using a standard reference table that assumes the first month has 31 days, I should get x years, 0 months, and 29 days. While that answer is absolutely correct, the presentation can get sticky when someone looks at the birth date and the achievement date and argues that should be x years 1 month and 1 day. If you have to write more than 3 words to explain it on your presentation, you've lost your argument!How it's presented is part of the issue when the audience requires that it be formatted in YMD. Going back to my original question though: I have a function that is somewhat messy to do the formatting. I was wondering if anyone had a simple elegant solution they've used to do the formatting. Thomas Abraham SSCrazy Group: General Forum Members Points: 2333 Visits: 2254 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. Please don't go. The drones need you. They look up to you.Connect to me on LinkedIn Thomas Abraham SSCrazy Group: General Forum Members Points: 2333 Visits: 2254 Dennis Wagner-347763 (1/16/2014)Thomas, I like the way you are thinking. My solution was to build a function that would use the elapsed days to do the formatting. While I agree that using a reference table would give a consistent determinate result, the issue would be how do we build that reference table.That's why I'd use a reference DATE, instead of a table. No construction required.We can't just use 30 days in a month, or we'll run out of months before we run out of days in a year. There are applications where this IS done. Ever seen financial calculators for loans? They use standard 30 day months. But, that's a different animal I suppose.The issue then becomes presentation and acceptance. If I compare someone born on February 15th with a date on March 16th, using a standard reference table that assumes the first month has 31 days, I should get x years, 0 months, and 29 days. While that answer is absolutely correct, the presentation can get sticky when someone looks at the birth date and the achievement date and argues that should be x years 1 month and 1 day. If you have to write more than 3 words to explain it on your presentation, you've lost your argument!How it's presented is part of the issue when the audience requires that it be formatted in YMD. Going back to my original question though: I have a function that is somewhat messy to do the formatting. I was wondering if anyone had a simple elegant solution they've used to do the formatting.Seems like you only have two choices here:1. Recognize that there is no simple elegant solution when dealing with our calendar, which is neither simple nor elegant. This means you have to do a modified version of the three DATEDIFFs, which I imagine is what you are already doing, e.g. "years = DATEDIFF(yy, datea, dateb), less 1 if month and day of dateb falls before month and day of datea".2. Make a single simplifying assumption (a reference date) that makes all time periods comparable, regardless of when they occur, realizing that there may be some disagreement with the results.Both solutions have drawbacks. Which you choose depends on which drawbacks you find acceptable. Please don't go. The drones need you. They look up to you.Connect to me on LinkedIn