|
Subject:
|
calculating time differences
|
|
Posted By:
|
vincee
|
Post Date:
|
1/23/2006 3:09:06 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
|
|
Reply By:
|
vincee
|
Reply Date:
|
1/23/2006 3:51:43 AM
|
need to add on:
those twe columns datatype is int
do i need to convert it to datetime format 1st?
if yes, how?
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
1/24/2006 12:29:28 AM
|
show some sample data and desired results
Jim
|
|
Reply By:
|
vincee
|
Reply Date:
|
1/24/2006 5:38:58 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.
|
|
Reply By:
|
Gert
|
Reply Date:
|
1/24/2006 6:32:48 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:
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
|
|
Reply By:
|
Gert
|
Reply Date:
|
1/24/2006 6:34:38 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
|
|
Reply By:
|
SqlMenace
|
Reply Date:
|
1/24/2006 10:02:28 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/
|
