Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Calculating Duration Using DATETIME Start and End Dates (SQL Spackle) Expand / Collapse
Author
Message
Posted Thursday, January 16, 2014 7:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:35 AM
Points: 1,825, Visits: 2,175
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 YmX
FROM #JBMTest;



I got:

Msg 8115, Level 16, State 2, Line 15
Arithmetic 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
Post #1531589
Posted Thursday, January 16, 2014 8:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
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 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

Helpful Links:
How to post code problems
How to post performance problems
Post #1531607
Posted Thursday, January 16, 2014 8:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
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 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

Helpful Links:
How to post code problems
How to post performance problems
Post #1531615
Posted Thursday, January 16, 2014 8:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 5:43 AM
Points: 48, Visits: 203
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 elapsed

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,666

However, 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.
Post #1531619
Posted Thursday, January 16, 2014 9:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
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 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

Helpful Links:
How to post code problems
How to post performance problems
Post #1531631
Posted Thursday, January 16, 2014 9:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:51 AM
Points: 1,984, Visits: 5,160
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 YmX
FROM #JBMTest;



I got:

Msg 8115, Level 16, State 2, Line 15
Arithmetic 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)


Post #1531649
Posted Thursday, January 16, 2014 9:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:35 AM
Points: 1,825, Visits: 2,175
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 elapsed


Yes, 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,666

However, 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
Post #1531669
Posted Thursday, January 16, 2014 10:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 5:43 AM
Points: 48, Visits: 203
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.
Post #1531682
Posted Thursday, January 16, 2014 10:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:35 AM
Points: 1,825, Visits: 2,175
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
Post #1531683
Posted Thursday, January 16, 2014 10:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:35 AM
Points: 1,825, Visits: 2,175
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
Post #1531703
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse