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 ««12

total time difference between more than two dates Expand / Collapse
Author
Message
Posted Monday, April 14, 2008 11:33 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 35,589, Visits: 32,180
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."

(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 #484566
Posted Monday, April 14, 2008 11:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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, ETC
Property 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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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, ETC
Property 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-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 35,589, Visits: 32,180
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."

(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 #484570
Posted Monday, April 14, 2008 11:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
Modified code:

declare @D1 datetime, @D2 datetime

select @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 AM
1 Jan 1800 and 1 Jan 1900
28 Feb 2008 and 1 Mar 2008 -- To test for leap years
12 Dec 2007 and 12 Dec 2007 -- To test for 0
12 Dec 2007 and 12 Dec 2008

Stopped 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, ETC
Property 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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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, ETC
Property 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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 8, 2010 7:33 AM
Points: 25, Visits: 45
Humorous, entertaining and very informative. Thanks!:)
Post #484617
Posted Friday, April 18, 2008 4:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 35,589, Visits: 32,180
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."

(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 #487097
Posted Thursday, July 18, 2013 7:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse