How to get total number of hours between two dates in sql server?

Consider two dates 2开发者_StackOverflow中文版010-03-18 22:30:45 and 2010-03-19 03:30:15 .... How to get the number of hours and minutes in between the two dates in sql server.....

@codeka answered with the hours part (from your title) but in the body of your question you asked for hours and minutes so, here is one way

 select DATEDIFF(hh, @date1, @date2) as Hours_Difference,   
    DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, @date1, @date2),@date1),@date2) as Minutes_Difference

What this does in the first part is what @codeka showed. It gives you the datediff between the two dates in actual full hours. The second term in the sql gives the datediff in minutes between the (first date + the hours elapsed) and the second date. You have to eliminate the hours from the equation in the minutes part or you will get the actual minutes between the dates. Datediff and its allowed Datepart identifiers can be researched here:
http://msdn.microsoft.com/en-us/library/ms189794.aspx

You want the DATEDIFF function:

SELECT DATEDIFF(hh, @date1, @date2)

The problem with William Salzman's answer is that it returns strange answers if the first time is not on the hour. So 10:30 to 12:00 gives 2 hours and -30 minutes.

If that isn't what you want, then this will give you 1 hour and 30 minutes:

select 
    CONVERT(int,DATEDIFF(mi, @date1, @date2) / 60) as Hrs_Difference,
    CONVERT(int,DATEDIFF(mi, @date1, @date2) % 60) as Mins_Difference

DATEDIFF function will solve your problem of getting hours difference.

you can check this as :

select DATEDIFF(hh, getdate()-1, GETDATE()) HoursDifference

It will give you result of 24 hours

Thanks

It seems there are various ways to answer this, but for me, I've always believe in the notion of starting small - as in get the overall value and then convert it UP to the answer you want.

Assuming date2 is greater than date1:

A simple mathematical difference multiplied by 24 hours in a day works:

CAST(date2 - date1 as NUMERIC(18,4)) * 24.00 AS [HrsDiff]

Or using Will's answer using [min] for minutes:

CAST(DATEDIFF(mi, date1, date2) AS NUMERIC(18,4)) / 60 AS [HrsDiff]

So the minute total is divided by 60 to give you the hours.

The first option gives you a big decimal, whereas the 2nd option rounds it up to the nearest decimal.