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

DATEDIFF with days and hours Expand / Collapse
Author
Message
Posted Monday, February 18, 2013 4:53 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:11 AM
Points: 853, Visits: 787
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
Post #1421137
Posted Monday, February 18, 2013 5:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:03 AM
Points: 235, Visits: 831
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)

Post #1421153
Posted Monday, February 18, 2013 5:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:11 AM
Points: 853, Visits: 787
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
Post #1421156
Posted Monday, February 18, 2013 5:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:34 AM
Points: 2,666, Visits: 4,736
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 ALL
SELECT 1, '2013-02-13 12:42:55', '2013-02-14 14:30:50' UNION ALL
SELECT 1, '2013-02-12 15:04:32', '2013-02-15 12:22:25' UNION ALL
SELECT 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 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/
Post #1421168
Posted Monday, February 18, 2013 6:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:03 AM
Points: 235, Visits: 831
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!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:11 AM
Points: 853, Visits: 787
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
Post #1421177
Posted Monday, February 18, 2013 6:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:03 AM
Points: 235, Visits: 831
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


Post #1421183
Posted Monday, February 18, 2013 5:16 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
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 "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 #1421385
Posted Monday, February 18, 2013 11:48 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:11 AM
Points: 853, Visits: 787
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
Post #1421446
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse