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

How to get difference between two dates in days and hours? Expand / Collapse
Author
Message
Posted Sunday, April 7, 2013 1:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, September 13, 2014 10:47 AM
Points: 86, Visits: 249
Hi, I'm looking code to find difference between two dates in days and hours.
Example:
date1: 2013-04-07 14:45:41.013 - date2: 2013-04-05 10:45:41.013

I need output like 2 days and 6 hours?
Post #1439665
Posted Sunday, April 7, 2013 2:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:43 PM
Points: 5,615, Visits: 25,197
Use the DATEDIFF functions ...

for example

SELECT DATEDIFF ( datepart , startdate , enddate )

This will return the number of days difference.

SELECT DATEDIFF(d,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')
returns 2 days

This returns the total number of hours difference (52)

SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')

--combining every thing I think will give you what you are looking for:

	SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24 AS 'Days'

,DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013') -

(DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24)*24 AS 'Hours'
Result:

Days Hours
2 4



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1439666
Posted Sunday, April 7, 2013 9:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, September 13, 2014 10:47 AM
Points: 86, Visits: 249
Thanks, It's worked.
Post #1439676
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse