InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 07:08:29 GMT20http://www.sqlservercentral.com/Forums/Topic1421137-3077-1.aspxThanks 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 wellMon, 18 Feb 2013 23:48:12 GMTIan C0ckcrofthttp://www.sqlservercentral.com/Forums/Topic1421137-3077-1.aspx[quote][b]Gabriel P (2/18/2013)[/b][hr][quote][b]Ian C0ckcroft (2/18/2013)[/b][hr]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?[/quote]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[code="sql"]Declare @create_date datetime = '1/1/2013', @execution_date DATETIME = GETDATE();SELECT CAST(@execution_date - @create_date AS REAL)[/code][/quote]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".Mon, 18 Feb 2013 17:16:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1421137-3077-1.aspx[quote][b]Ian C0ckcroft (2/18/2013)[/b][hr]Thanks Kingston, looks much neater, will try that.Gabriel, its just 6+5 =11 and should be 6 + .5 = 6.5 :-)[/quote]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 days[b]6 days + 5 hour = 6.208 days[/b]6 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 daysMon, 18 Feb 2013 06:38:12 GMTGabriel Phttp://www.sqlservercentral.com/Forums/Topic1421137-3077-1.aspxThanks Kingston, looks much neater, will try that.Gabriel, its just 6+5 =11 and should be 6 + .5 = 6.5 :-)Mon, 18 Feb 2013 06:17:11 GMTIan C0ckcrofthttp://www.sqlservercentral.com/Forums/Topic1421137-3077-1.aspx[quote][b]Ian C0ckcroft (2/18/2013)[/b][hr]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.[/quote]Unless you know of a timekeeping system I am unfamiliar with, 6 days + 5 hours is not the same as 6.5 days :-)Mon, 18 Feb 2013 06:00:55 GMTGabriel Phttp://www.sqlservercentral.com/Forums/Topic1421137-3077-1.aspxThis should help you[code="sql"]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[/code]Edit: Added some comments and changed the query( "<" condition changed to "<=" )Mon, 18 Feb 2013 05:52:55 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic1421137-3077-1.aspxTo 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.Mon, 18 Feb 2013 05:37:44 GMTIan C0ckcrofthttp://www.sqlservercentral.com/Forums/Topic1421137-3077-1.aspx[quote][b]Ian C0ckcroft (2/18/2013)[/b][hr]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?[/quote]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[code="sql"]Declare @create_date datetime = '1/1/2013', @execution_date DATETIME = GETDATE();SELECT CAST(@execution_date - @create_date AS REAL)[/code]Mon, 18 Feb 2013 05:31:47 GMTGabriel Phttp://www.sqlservercentral.com/Forums/Topic1421137-3077-1.aspxHi 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?Mon, 18 Feb 2013 04:53:12 GMTIan C0ckcroft