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

 total time difference between more than two dates Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, April 14, 2008 11:33 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 12:11 PM Points: 42,082, Visits: 39,475
 Sorry, Gus... we're posting in parallel... The -1 addition will work but I think the tweek needs to be extended to the days column, as well. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #484566
 Posted Monday, April 14, 2008 11:35 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 Yeah. It's not precisely a rounding error. It's a "1 Jan" does not equal 0 error. Needs -1 on the month and day columns because of that. - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #484567
 Posted Monday, April 14, 2008 11:36 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 Only month and day need the -1. 0 = midnight, so hours, minutes, seconds, etc., don't need that. Years already has a -1900 (because that's 0 in datetime).Try it with that. - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #484568
 Posted Monday, April 14, 2008 11:42 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 12:11 PM Points: 42,082, Visits: 39,475
 Heh... you started it... was hoping you had some final 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." Helpful Links:How to post code problemsHow to post performance problems
Post #484570
 Posted Monday, April 14, 2008 11:43 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 Modified code:`declare @D1 datetime, @D2 datetimeselect @d1 = '1/30/2000 11:59:59 pm', @d2 = '1/31/2000 12:00:01 am'select datepart(year, @d2-@d1)-1900 as years,datepart(month, @d2-@d1)-1 as months,datepart(day, @d2-@d1)-1 as days,datepart(hour, @d2-@d1) as hours,datepart(minute, @d2-@d1) as minutes,datepart(second, @d2-@d1) as seconds,datepart(millisecond, @d2-@d1) as milliseconds`Tried it with:1 Jan 2000 11:59:59 PM and 2 Jan 2000 12:00:01 AM1 Jan 1800 and 1 Jan 190028 Feb 2008 and 1 Mar 2008 -- To test for leap years12 Dec 2007 and 12 Dec 2007 -- To test for 012 Dec 2007 and 12 Dec 2008Stopped there. The first 4 worked perfectly. The last one gave back 1 year and 1 day. It's because of the leap year, since the Date2-Date1 float calculation gives 2 Jan 1901. Makes the whole thing not work without various Case statements. - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #484573
 Posted Monday, April 14, 2008 11:48 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 Because of the leap year subtraction problem, it won't work if one of the dates is before, and one is after, 29 Feb in a leap year, and the two are in different years. I'm sure it could be corrected for, but I'm not sure it's worth it.If it were sticking to days, like yours, it would work. But adding in months and years breaks it. - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #484576
 Posted Monday, April 14, 2008 1:27 PM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, April 27, 2016 7:26 AM Points: 26, Visits: 46
 Humorous, entertaining and very informative. Thanks!:)
Post #484617
 Posted Friday, April 18, 2008 4:24 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, January 14, 2015 8:59 PM Points: 1,038, Visits: 445
 I think that was the longest and most comprehensive reply I've ever seen on here :) Shame it wasn't used Hopefully when someone else asks the same question they'll search the forums and at least find lots of information in Jeff's answer!
Post #487035
 Posted Friday, April 18, 2008 5:53 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 12:11 PM Points: 42,082, Visits: 39,475
 Thanks folks. I really appreciate the feedback especially on a long one like that. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #487097
 Posted Thursday, July 18, 2013 7:37 PM
 Forum Newbie Group: General Forum Members Last Login: Thursday, July 18, 2013 7:39 PM Points: 1, Visits: 4
 this works for me:SET @StartDate = (Select '2013-07-19 12:20:07.097')Set @EndDate = (SELECT '2013-07-19 15:02:38.000') SET @RESULT = (SELECT 'Starts in: ' + STR(FLOOR(CAST(@newDate-@RunDate AS FLOAT)),1) + ' days ' + convert(varchar,datepart(hour,@EndDate-@StartDate)) + ' hrs ' + convert(varchar,datepart(minute,@EndDate-@StartDate)) + ' mins')Result is: 'Starts in: 0 days 2 hrs 42 mins'
Post #1475246

 Permissions