Question

求教sql语句两个日期、时间字段相减得到天数小时分秒的问题

select requestid,userid,workflowid,receivedate,receivetime,operatedate,operatetimefrom workflow_currentoperator where workflowid=297 and requestid=110215 order by receivedate,receivetime如下图，通过上述语句查询处结果；希望得到字段：operatedate,operatetime；减去字段receivedate,receivetime；得到一个天数小时分秒的结果。 请教下语句怎么写？

Answer 1

select requestid,userid,workflowid,receivedate,receivetime,operatedate,operatetime,
cast(day as varchar)+'天'+cast((hour-day*24) as varchar)+'小时'+
cast((minute-hour*60) as varchar)+'分'+
cast((second-minute*60) as varchar)+'秒' result
from(
select requestid,userid,workflowid,receivedate,receivetime,operatedate,operatetime,
datediff(d, CONVERT(datetime,receivedate+' '+receivetime,20), 
CONVERT(datetime,operatedate+' '+operatetime,20)) day,
datediff(hh, CONVERT(datetime,receivedate+' '+receivetime,20), 
CONVERT(datetime,operatedate+' '+operatetime,20)) hour,
datediff(n, CONVERT(datetime,receivedate+' '+receivetime,20), 
CONVERT(datetime,operatedate+' '+operatetime,20)) minute,
datediff(s, CONVERT(datetime,receivedate+' '+receivetime,20), 
CONVERT(datetime,operatedate+' '+operatetime,20)) second
from workflow_currentoperator 
where workflowid=297 and requestid=110215 
) t
order by receivedate,receivetime