반응형

/********************************************************************************************
-- Title : [SQL2008] 두 일자의 시간차를 시:분:초로 변환
-- Key word : 시간 날짜 날자 time
********************************************************************************************/

declare @d1 datetime, @d2 datetime, @elapse_second int;
select @D1 = '2012-09-06 18:01:45.000', @d2 = '2012-09-10 09:26:30.000';

set @elapse_second = datediff(s, @d1, @D2);

select case when len(convert(nvarchar(10), @elapse_second / 3600)) = 1
            then '0' + convert(nvarchar(10), @elapse_second / 3600)
            else convert(nvarchar(10), @elapse_second / 3600)
       end
     + ':'
     + case when len(convert(nvarchar(10), (@elapse_second % 3600) / 60)) = 1
            then '0' + convert(nvarchar(10), (@elapse_second % 3600) / 60)
            else convert(nvarchar(10), (@elapse_second % 3600) / 60)
       end + ':'
     + case when len(convert(nvarchar(10), (@elapse_second % 3600) % 60)) = 1
            then '0' + convert(nvarchar(10), (@elapse_second % 3600) % 60)
            else convert(nvarchar(10), (@elapse_second % 3600) % 60)
       end;

반응형

+ Recent posts