CODEWITHSUNDEEP
sqlsql-servert-sql
Bella
Bella

Reputation: 87

Datediff for date and hour (24 hours)

I know it's a simple question, but I'm stuck. I tried DateDiff day and hour seperately but I want my date can detect my time if its over 24 hours.

For example: 

Date1: 20/12/2011
Time1: 9:00

Date2: 21/12/2011
Time2 : 13:00

Days Taken: 1
Time Taken: 28

I want to calculate the days and hours, so 1 day and 4 hours is my desired result.

Can anyone please help me how to do with this? Appreciate.

Upvotes: 3

Views: 4679

Answers (3)

Kaf
Kaf

Reputation: 33809

In sql-server;

declare @d1 datetime = '20111220 09:00:00'
declare @d2 datetime = '20111221 13:00:00'

select hours/24 daysTaken, hours%24 hoursTaken
from (
    select datediff(hh,@d1,@d2) hours
) A

--Results
daysTaken hoursTaken
   1          4

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460048

Perhaps with the help of MODULO:

SELECT (
  DATEDIFF(dd,
    CONVERT(datetime,'20/12/2011 09:00:00',104),
    CONVERT(datetime,'21/12/2011 13:00:00',104))
 ) AS [days taken],
(
  DATEDIFF(hh,
    CONVERT(datetime,'20/12/2011 09:00:00',104),
    CONVERT(datetime,'21/12/2011 13:00:00',104))
   % 24
 ) AS [hours taken]

Result:

DAYS TAKEN      HOURS TAKEN
    1                4

DEMO

Upvotes: 4

Madhivanan
Madhivanan

Reputation: 13700

Assuming you are using SQL Server

Select datediff(hour,date1,date2)/24.0 from table

Upvotes: -1

Related Questions