很简单求两个时间相差多长时间 !
例如 '07:36:00' 减 '16:41:00' 等于 -08:55:00用下面这个方法只能得到一个-9 结果不符合我的要求 select datediff(hh,'16:41:00','07:36:00')请高手帮忙 ! 谢谢 !
例如 '07:36:00' 减 '16:41:00' 等于 -08:55:00用下面这个方法只能得到一个-9 结果不符合我的要求 select datediff(hh,'16:41:00','07:36:00')请高手帮忙 ! 谢谢 !
declare @dt1 as varchar(8)
declare @dt2 as varchar(8)
set @dt1 = '07:36:00'
set @dt2 = '16:41:00'select val =
right('00'+cast(datediff(s , '2000-01-01 ' + @dt1 , '2000-01-01 ' + @dt2) / (60*60) as varchar),2) + ':' +
right('00'+cast((datediff(s , '2000-01-01 ' + @dt1 , '2000-01-01 ' + @dt2) % (60*60))/60 as varchar),2) + ':' +
right('00'+cast((datediff(s , '2000-01-01 ' + @dt1 , '2000-01-01 ' + @dt2) % (60*60))%60 as varchar),2)
/*
val
--------------
09:05:00(所影响的行数为 1 行)
*/
select cast(datediff(ss,'16:41:00','07:36:00')/3600 as varchar) + ':' + case when len(replace(cast(datediff(ss,'16:41:00','07:36:00')%3600/60 as varchar),'-','')) = 1 then '0' + replace(cast(datediff(ss,'16:41:00','07:36:00')%3600/60 as varchar),'-','') else replace(cast(datediff(ss,'16:41:00','07:36:00')%3600/60 as varchar),'-','') end +':' + case when len(replace(cast(datediff(ss,'16:41:00','07:36:00')%60 as varchar),'-','')) = 1 then '0' + replace(cast(datediff(ss,'16:41:00','07:36:00')%60 as varchar),'-','') else replace(cast(datediff(ss,'16:41:00','07:36:00')%60 as varchar),'-','') end
declare @dt1 as varchar(30)
declare @dt2 as varchar(30)
set @dt1 = '16:41:00'
set @dt2 = '07:36:00'if @dt1 < @dt2
begin
set @dt1 = '2000-01-01 ' + @dt1
set @dt2 = '2000-01-01 ' + @dt2
select val =
right('00'+cast(datediff(s , @dt1 , @dt2) / (60*60) as varchar),2) + ':' +
right('00'+cast((datediff(s , @dt1 , @dt2) % (60*60))/60 as varchar),2) + ':' +
right('00'+cast((datediff(s , @dt1 , @dt2) % (60*60))%60 as varchar),2)
end
else
begin
set @dt1 = '2000-01-01 ' + @dt1
set @dt2 = '2000-01-02 ' + @dt2
select val = '-' +
right('00'+cast(datediff(s , @dt1 , @dt2) / (60*60) -6 as varchar),2) + ':' +
right('00'+cast((datediff(s , @dt1 , @dt2) % (60*60))/60 as varchar),2) + ':' +
right('00'+cast((datediff(s , @dt1 , @dt2) % (60*60))%60 as varchar),2)
end
/*
val
---------------
-08:55:00(所影响的行数为 1 行)
*/
'07:36:00' 减 '16:41:00' 应该等于 -09:05:00 所以上面的应该为:declare @dt1 as varchar(30)
declare @dt2 as varchar(30)
set @dt1 = '16:41:00'
set @dt2 = '07:36:00'
set @dt1 = '2000-01-01 ' + @dt1
set @dt2 = '2000-01-01 ' + @dt2if @dt1 <= @dt2
select val =
right('00'+cast(datediff(s , @dt1 , @dt2) / (60*60) as varchar),2) + ':' +
right('00'+cast((datediff(s , @dt1 , @dt2) % (60*60))/60 as varchar),2) + ':' +
right('00'+cast((datediff(s , @dt1 , @dt2) % (60*60))%60 as varchar),2)
else
select val = '-' +
right('00'+cast(abs(datediff(s , @dt1 , @dt2)) / (60*60) as varchar),2) + ':' +
right('00'+cast(abs((datediff(s , @dt1 , @dt2)) % (60*60))/60 as varchar),2) + ':' +
right('00'+cast(abs((datediff(s , @dt1 , @dt2)) % (60*60))%60 as varchar),2)/*
val
---------------
-09:05:00(所影响的行数为 1 行)*/
declare @dt2 as varchar(8)
set @dt1 = '07:36:00'
set @dt2 = '16:41:00'
select
ltrim(case when datepart(mi,@dt1) < datepart(mi,@dt2) then datepart(hh,@dt1) - datepart(hh,@dt2) +1
else datepart(hh,@dt1) - datepart(hh,@dt2) end)+':'+ltrim(case when datepart(mi,@dt1) < datepart(mi,@dt2) then datepart(mi,@dt1) - datepart(mi,@dt2) + 60
else datepart(mi,@dt1) - datepart(mi,@dt2) end)+':00'/*----------------------------
-8:55:00(所影响的行数为 1 行)
*/
declare @dt2 datetime
declare @dt3 datetime
set @dt1 = '16:41:00'
set @dt2 = '18:36:00'
set @dt3 = '07:36:00'
select case when @dt1>@dt2 then substring(convert(char(20),@dt1-@dt2,120),12,8)
else '-'+substring(convert(char(20),@dt2-@dt1,120),12,8) end
select case when @dt1>@dt3 then substring(convert(char(20),@dt1-@dt3,120),12,8)
else '-'+substring(convert(char(20),@dt3-@dt1,120),12,8) end
--01:55:00
--09:05:00
on a.UserID = b.EmpID and a.[DateTime] = @today
-->
a.[MorTime] = case when EndOfVacation between '08:30:00' and '09:30:00 then '-'+convert(char(8),cast(EndOfVacation as datetime)-'08:30:00',108) else '0' end
select '-' + right( '0' + replace(cast(datediff(ss,'16:41:00','07:36:00')/3600 as varchar),'-',''),2) + ':' + case when len(replace(cast(datediff(ss,'16:41:00','07:36:00')%3600/60 as varchar),'-','')) = 1 then '0' + replace(cast(datediff(ss,'16:41:00','07:36:00')%3600/60 as varchar),'-','') else replace(cast(datediff(ss,'16:41:00','07:36:00')%3600/60 as varchar),'-','') end +':' + case when len(replace(cast(datediff(ss,'16:41:00','07:36:00')%60 as varchar),'-','')) = 1 then '0' + replace(cast(datediff(ss,'16:41:00','07:36:00')%60 as varchar),'-','') else replace(cast(datediff(ss,'16:41:00','07:36:00')%60 as varchar),'-','') end