calculating time differences

vincee · January 23rd, 2006, 04:09 AM

i need to calculate the time differences in minute.
can anybody help me with this?
i have this 2 columns which is:
   -origination_time
   -due_time

i use this function:
  SELECT org_time, due_time,
         (datediff (n, org_time, due_time)) 'mins'

but it calculate in 100 instead of 60

can anyone help me out with this?
thanks

vincee · January 23rd, 2006, 04:51 AM

need to add on:
those twe columns datatype is int
do i need to convert it to datetime format 1st?
if yes, how?

jbenson001 · January 24th, 2006, 01:29 AM

show some sample data and desired results

Jim

vincee · January 24th, 2006, 06:38 AM

The results:

org_time due_time mins
-------- -------- ------
1629 1630 1
751 800 49
1521 1530 9
1123 1130 7
1133 1145 12
1147 1200 53

Desired result:

org_time due_time mins
-------- -------- ------
1629 1630 1
751 800 9
1521 1530 9
1123 1130 7
1133 1145 12
1147 1200 13

Thank you.

Gert · January 24th, 2006, 07:32 AM

Hi.

org_time and due_time should naturally be datetimes to account for shifting of dates etc.

However, if that is not possible, I belive this query should work:

Code:

SELECT DATEDIFF(mi,
         CAST('20060101 '+LEFT(RIGHT(('0' + CAST(org_time as varchar)),4),2) + ':'+RIGHT(RIGHT(('0' + CAST(org_time as varchar)),4),2) as datetime),
        CAST('20060101 '+LEFT(RIGHT(('0' + CAST(due_time as varchar)),4),2) + ':'+RIGHT(RIGHT(('0' + CAST(due_time as varchar)),4),2) as datetime)
    )
FROM tblYourTable

I'm sure there is better ways to solve your problem, but I hope this will help.

Gert

Gert · January 24th, 2006, 07:34 AM

Just be aware that my sollution doesn't work across different dates. The org_time and due_time must be the same date.

Gert

SqlMenace · January 24th, 2006, 11:02 AM

If you only care about times you can also do a kludge like this

create table timespan(startime int,endtime int)
insert into timespan
select 1629 , 1630 union all
select 751 , 800 union all
select 1521 , 1530 union all
select 1123 , 1130 union all
select 1133 , 1145 union all
select 1147 , 1200

select startime org_time,endtime due_time,
case when (endtime-startime) - 40 < 0 then
endtime-startime else (endtime-startime) - 40 end mins
from timespan

âI sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/