¿Como sacar la diferencia en DIAS, HORAS, MINUTOS Y SEGUNDOS?
select
sum(datediff(second, FechaAl, FechaOk)) / (24 * 60 * 60) as dias,
(sum(datediff(second, FechaAl, FechaOk)) % (24 * 60 * 60)) / (3600) as horas,
((sum(datediff(second, FechaAl, FechaOk)) % (24 * 60 * 60)) % (3600)) /
(60) as minutos,
((sum(datediff(second, FechaAl, FechaOk)) % (24 * 60 * 60)) % (3600)) %
(60) as segundos
from
(
select
cast(cFechaAl as datetime) as FechaAl,
isnull(cast(cFechaOk as datetime), dateadd(second, (FAxDia * 24 * 60 *
60) + datediff(second, convert(varchar(10), getdate(), 126),
convert(varchar(10), getdate(), 126) + 'T' + FAxHora), cast(cFechaAl as
datetime))) as FechaOk
from
v1 -- AQUI PON EL NOMBRE DE LA VISTA
) as t
go
Ejemplo:
use northwind
go
create table t1 (
cFechaAl varchar(25),
cFechaOk varchar(25),
FAxDia int,
FAxHora varchar(8)
)
go
insert into t1 values('2006-05-26 13:30:08', '2006-05-26 13:45:03', 0, '00:14:55')
insert into t1 values('2006-05-26 13:31:42', '2006-05-29 10:57:08', 2, '21:25:26')
insert into t1 values('2006-05-26 13:31:59', NULL, 10, '21:02:57')
go
select
sum(datediff(second, FechaAl, FechaOk)) / (24 * 60 * 60) as dias,
(sum(datediff(second, FechaAl, FechaOk)) % (24 * 60 * 60)) / (3600) as horas,
((sum(datediff(second, FechaAl, FechaOk)) % (24 * 60 * 60)) % (3600)) /
(60) as minutos,
((sum(datediff(second, FechaAl, FechaOk)) % (24 * 60 * 60)) % (3600)) %
(60) as segundos
from
(
select
cast(cFechaAl as datetime) as FechaAl,
isnull(cast(cFechaOk as datetime), dateadd(second, (FAxDia * 24 * 60 *
60) + datediff(second, convert(varchar(10), getdate(), 126),
convert(varchar(10), getdate(), 126) + 'T' + FAxHora), cast(cFechaAl as
datetime))) as FechaOk
from
t1
) as t
go
drop table t1
go