-
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))
-
day diff
Worked like a charm!!
nosepicker you are a SQL artist !
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|