Full time diff between dates

Hi all

I am trying to get the day diff in days, hours, minutes between 2 dates

I was able to do so in Oracle but haven't succeeded in SQL server


Here is the code:

select

DateDiff(Day, PROCESS.date_created, PROCESS.date_modified)as days,

datediff(hour,PROCESS.date_created, PROCESS.date_modified) as hours,

datediff(minute,PROCESS.date_created, PROCESS.date_modified) as minues

from

PROCESS

How ever they are ther are not calculated all together



How can I combine them into one full time diff (days, hours, minutes) ?

can i get sql software from u.

Here's one way to do it:

declare @date1 datetime, @date2 datetime

set @date1 = '2007-01-01 00:00:00'
set @date2 = '2007-01-02 01:15:00'


SELECT DATEDIFF(day, 0, DATEADD(minute, DATEDIFF(minute, @date1, @date2), 0))
SELECT DATEPART(hour, CONVERT(varchar, DATEADD(minute, DATEDIFF(minute, @date1, @date2), 0), 108))
SELECT DATEPART(minute, CONVERT(varchar, DATEADD(minute, DATEDIFF(minute, @date1, @date2), 0), 108))

Actually, that was a little bit of overkill. I simplified it a bit:

declare @date1 datetime, @date2 datetime

set @date1 = '2007-01-01 00:00:00'
set @date2 = '2007-01-02 01:15:00'


SELECT DATEDIFF(day, 0, DATEADD(minute, DATEDIFF(minute, @date1, @date2), 0))
SELECT DATEPART(hour, DATEADD(minute, DATEDIFF(minute, @date1, @date2), 0))
SELECT DATEPART(minute, DATEADD(minute, DATEDIFF(minute, @date1, @date2), 0))

Worked like a charm!!

nosepicker you are a SQL artist !:)