Log in  ::  Register  ::  Not logged in

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

 DATEDIFF with days and hours Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, February 18, 2013 4:53 AM
 SSC Eights! Group: General Forum Members Last Login: Friday, November 4, 2016 2:07 AM Points: 868, Visits: 863
 Hi guys, I am trying to calculate the diffs between to days as a decimal. The hours will an easier wayybe in decimal.i can do it in varchar, but i need to be able to agrigate my value.eg. datediff(DD, [CreatedOn],getdate()) + CAST(DATEPART(HH, GETDATE())- DATEPART(HH, [CreatedOn]) AS REAL)/10this gives me the results i need.is there an easier way? Ian CockcroftMCITP BI Specialist
Post #1421137
 Posted Monday, February 18, 2013 5:31 AM
 SSC Veteran Group: General Forum Members Last Login: Yesterday @ 10:04 PM Points: 245, Visits: 933
 Ian C0ckcroft (2/18/2013)Hi guys, I am trying to calculate the diffs between to days as a decimal. The hours will an easier wayybe in decimal.i can do it in varchar, but i need to be able to agrigate my value.eg. datediff(DD, [CreatedOn],getdate()) + CAST(DATEPART(HH, GETDATE())- DATEPART(HH, [CreatedOn]) AS REAL)/10this gives me the results i need.is there an easier way?Why are you dividing the difference in hours by 10? I'm guessing that's supposed to be 24 with a conversation to a float or decimal type.Either way I think this is what you are looking for`Declare @create_date datetime = '1/1/2013', @execution_date DATETIME = GETDATE();SELECT CAST(@execution_date - @create_date AS REAL)`
Post #1421153
 Posted Monday, February 18, 2013 5:37 AM
 SSC Eights! Group: General Forum Members Last Login: Friday, November 4, 2016 2:07 AM Points: 868, Visits: 863
 To get the hours as a decimal. else its 6 days + 5 hours = 11 days and should be 6 days + .5 hours = 6.5 days.Not 100% sure this will work yet. Ian CockcroftMCITP BI Specialist
Post #1421156
 Posted Monday, February 18, 2013 5:52 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 4:52 AM Points: 2,917, Visits: 4,954
 This should help you`DECLARE @table TABLE( ID INT, StartDate DATETIME, EndDate DATETIME)INSERT @table( ID, StartDate, EndDate )SELECT 1, '2013-02-17 10:33:10', '2013-02-17 20:14:40' UNION ALLSELECT 1, '2013-02-13 12:42:55', '2013-02-14 14:30:50' UNION ALLSELECT 1, '2013-02-12 15:04:32', '2013-02-15 12:22:25' UNION ALLSELECT 1, '2013-02-16 20:08:18', '2013-02-18 02:10:10'SELECT T.ID, T.StartDate, T.EndDate, CASE WHEN DATEADD( DAY, DATEDIFF( DAY, T.StartDate, T.EndDate ), T.StartDate ) <= T.EndDate THEN DATEDIFF( DAY, T.StartDate, T.EndDate ) ELSE DATEDIFF( DAY, T.StartDate, T.EndDate ) - 1 END + --====================Gives you the days ( CASE WHEN DATEADD( MINUTE, DATEDIFF( MINUTE, T.StartDate, T.EndDate ), T.StartDate ) <= T.EndDate THEN DATEDIFF( MINUTE, T.StartDate, T.EndDate ) ELSE DATEDIFF( MINUTE, T.StartDate, T.EndDate ) - 1 END % ( 60 * 24 ) ) / 1440.00 AS Result --===============Gives you the hours in decimalsFROM @table AS T`Edit: Added some comments and changed the query( "<" condition changed to "<=" ) Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Modenhttp://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1421168
 Posted Monday, February 18, 2013 6:00 AM
 SSC Veteran Group: General Forum Members Last Login: Yesterday @ 10:04 PM Points: 245, Visits: 933
 Ian C0ckcroft (2/18/2013)To get the hours as a decimal. else its 6 days + 5 hours = 11 days and should be 6 days + .5 hours = 6.5 days.Not 100% sure this will work yet.Unless you know of a timekeeping system I am unfamiliar with, 6 days + 5 hours is not the same as 6.5 days
Post #1421170
 Posted Monday, February 18, 2013 6:17 AM
 SSC Eights! Group: General Forum Members Last Login: Friday, November 4, 2016 2:07 AM Points: 868, Visits: 863
 Thanks Kingston, looks much neater, will try that.Gabriel, its just 6+5 =11 and should be 6 + .5 = 6.5 Ian CockcroftMCITP BI Specialist
Post #1421177
 Posted Monday, February 18, 2013 6:38 AM
 SSC Veteran Group: General Forum Members Last Login: Yesterday @ 10:04 PM Points: 245, Visits: 933
 Ian C0ckcroft (2/18/2013)Thanks Kingston, looks much neater, will try that.Gabriel, its just 6+5 =11 and should be 6 + .5 = 6.5 I'm sorry I did not make the point clear. I am concerned that your math in your formula is incorrect and if you are using this math to test your code against, your debugging is going to be off. 6 days + 5 hours doesn't equal 6.5 days, it equals 6.208 days.Please see below:6 days + 1 hour = 6.041 days6 days + 2 hour = 6.083 days6 days + 3 hour = 6.125 days6 days + 4 hour = 6.166 days6 days + 5 hour = 6.208 days6 days + 6 hour = 6.25 days6 days + 7 hour = 6.291 days6 days + 8 hour = 6.333 days6 days + 9 hour = 6.375 days6 days + 10 hour = 6.416 days6 days + 11 hour = 6.458 days6 days + 12 hour = 6.5 days6 days + 13 hour = 6.541 days6 days + 14 hour = 6.583 days6 days + 15 hour = 6.625 days6 days + 16 hour = 6.666 days6 days + 17 hour = 6.708 days6 days + 18 hour = 6.75 days6 days + 19 hour = 6.791 days6 days + 20 hour = 6.833 days6 days + 21 hour = 6.875 days6 days + 22 hour = 6.916 days6 days + 23 hour = 6.958 days6 days + 24 hour = 7 days
Post #1421183
 Posted Monday, February 18, 2013 5:16 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 12:37 PM Points: 42,035, Visits: 39,414
 Gabriel P (2/18/2013)Ian C0ckcroft (2/18/2013)Hi guys, I am trying to calculate the diffs between to days as a decimal. The hours will an easier wayybe in decimal.i can do it in varchar, but i need to be able to agrigate my value.eg. datediff(DD, [CreatedOn],getdate()) + CAST(DATEPART(HH, GETDATE())- DATEPART(HH, [CreatedOn]) AS REAL)/10this gives me the results i need.is there an easier way?Why are you dividing the difference in hours by 10? I'm guessing that's supposed to be 24 with a conversation to a float or decimal type.Either way I think this is what you are looking for`Declare @create_date datetime = '1/1/2013', @execution_date DATETIME = GETDATE();SELECT CAST(@execution_date - @create_date AS REAL)`Nice to see a kindred spirit. I normally use FLOAT for the same thing but that's basically the way I do it. Nice and simple.I wish they had made such simple calculations possible with the new date and time datatypes instead of trying to follow some bloody ANSI/ISO standard for the sake of the myth known as "portability". --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 #1421385
 Posted Monday, February 18, 2013 11:48 PM
 SSC Eights! Group: General Forum Members Last Login: Friday, November 4, 2016 2:07 AM Points: 868, Visits: 863
 Thanks Gabriel, see what you mean. I did think about that. Not sure how I'm going to handle that.These time diffs are going into a dimension and the reported on. Anyone looking at the report will wonder what 6.5 or 6.2 is. may be I should keep the fields seperate so it clearer, 6 days and 5 hours, or 6.2 days.But that has other implications.Thanks Jeff, will try that as well Ian CockcroftMCITP BI Specialist
Post #1421446

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.