

SSC Eights!
Group: General Forum Members
Last Login: Monday, March 9, 2015 2:03 AM
Points: 856,
Visits: 805


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)/10
this gives me the results i need. is there an easier way?
Ian Cockcroft MCITP BI Specialist




SSC Veteran
Group: General Forum Members
Last Login: Monday, August 31, 2015 6:56 PM
Points: 239,
Visits: 875


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)/10
this 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)




SSC Eights!
Group: General Forum Members
Last Login: Monday, March 9, 2015 2:03 AM
Points: 856,
Visits: 805


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 Cockcroft MCITP BI Specialist




SSCrazy
Group: General Forum Members
Last Login: Friday, August 14, 2015 8:39 AM
Points: 2,800,
Visits: 4,803


This should help you
DECLARE @table TABLE ( ID INT, StartDate DATETIME, EndDate DATETIME )
INSERT @table( ID, StartDate, EndDate ) SELECT 1, '20130217 10:33:10', '20130217 20:14:40' UNION ALL SELECT 1, '20130213 12:42:55', '20130214 14:30:50' UNION ALL SELECT 1, '20130212 15:04:32', '20130215 12:22:25' UNION ALL SELECT 1, '20130216 20:08:18', '20130218 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 decimals FROM @table AS T Edit: Added some comments and changed the query( "<" condition changed to "<=" )
Kingston Dhasian
How to post data/code on a forum to get the best help  Jeff Moden http://www.sqlservercentral.com/articles/Best+Practices/61537/




SSC Veteran
Group: General Forum Members
Last Login: Monday, August 31, 2015 6:56 PM
Points: 239,
Visits: 875


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




SSC Eights!
Group: General Forum Members
Last Login: Monday, March 9, 2015 2:03 AM
Points: 856,
Visits: 805


Thanks Kingston, looks much neater, will try that.
Gabriel, its just 6+5 =11 and should be 6 + .5 = 6.5
Ian Cockcroft MCITP BI Specialist




SSC Veteran
Group: General Forum Members
Last Login: Monday, August 31, 2015 6:56 PM
Points: 239,
Visits: 875


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 days 6 days + 2 hour = 6.083 days 6 days + 3 hour = 6.125 days 6 days + 4 hour = 6.166 days 6 days + 5 hour = 6.208 days 6 days + 6 hour = 6.25 days 6 days + 7 hour = 6.291 days 6 days + 8 hour = 6.333 days 6 days + 9 hour = 6.375 days 6 days + 10 hour = 6.416 days 6 days + 11 hour = 6.458 days 6 days + 12 hour = 6.5 days 6 days + 13 hour = 6.541 days 6 days + 14 hour = 6.583 days 6 days + 15 hour = 6.625 days 6 days + 16 hour = 6.666 days 6 days + 17 hour = 6.708 days 6 days + 18 hour = 6.75 days 6 days + 19 hour = 6.791 days 6 days + 20 hour = 6.833 days 6 days + 21 hour = 6.875 days 6 days + 22 hour = 6.916 days 6 days + 23 hour = 6.958 days 6 days + 24 hour = 7 days




SSCDedicated
Group: General Forum Members
Last Login: Today @ 1:10 AM
Points: 38,369,
Visits: 35,290


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)/10
this 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 "reebar" and is a "Modenism" for "RowByAgonizingRow".
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 TSQL, doesn't mean you SHOULDN'T." 22 Aug 2013
Helpful Links: How to post code problems How to post performance problems




SSC Eights!
Group: General Forum Members
Last Login: Monday, March 9, 2015 2:03 AM
Points: 856,
Visits: 805


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 Cockcroft MCITP BI Specialist



