求一个时间计算的问题 在线等 ！

[dcren118]

很简单求两个时间相差多长时间 ！
例如 '07:36:00' 减 '16:41:00' 等于 -08：55：00

用下面这个方法只能得到一个-9 结果不符合我的要求

select datediff(hh,'16:41:00','07:36:00')

请高手帮忙 ！ 谢谢 ！

[wynlc]

学习。。。

[cson_cson]

a.[MorTime] = （要实现这个功能的部分其实根据字段EndOfVacation的值来判断如果值小于9：30：00 并且大于 08：30：00计算迟到时间 谢谢）
-->
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

[zccmy22]

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 

[子陌红尘]

求出秒数之后自己换算一下

select datediff(ss,'16:41:00','07:36:00') 

[dawugui]

[Quote=引用楼主 dcren118 的帖子:]
很简单求两个时间相差多长时间 ！
例如 '07:36:00' 减 '16:41:00' 等于 -08：55：00

用下面这个方法只能得到一个-9 结果不符合我的要求

select datediff(hh,'16:41:00','07:36:00')

请高手帮忙 ！ 谢谢 ！
[/Quote]

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 行）
*/

[zccmy22]

select convert(datetime, cast(datediff(ss,'16:41:00','07:36:00')/3600 as varchar) + ':' + replace(cast(datediff(ss,'16:41:00','07:36:00')%3600/60 as varchar),'-','') +':' + replace(cast(datediff(ss,'16:41:00','07:36:00')%60 as varchar),'-',''),9)

[zccmy22]

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 

[dcren118]

楼上的高手们 ！得到的结果是 -8：55：00

我想要的结果是 -08：55：00 请高手在指点一下 谢谢 ！

[dawugui]

[Quote=引用 5 楼 dcren118 的回复:]
楼上的高手们 ！得到的结果是 -8：55：00

我想要的结果是 -08：55：00 请高手在指点一下 谢谢 ！
[/Quote]

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 行）
*/

[dawugui]

[Quote=引用楼主 dcren118 的帖子:]
很简单求两个时间相差多长时间 ！
例如 '07:36:00' 减 '16:41:00' 等于 -08：55：00

用下面这个方法只能得到一个-9 结果不符合我的要求

select datediff(hh,'16:41:00','07:36:00')

请高手帮忙 ！ 谢谢 ！
[/Quote]
你的结果本身就不对.
'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 ' + @dt2

if @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 行）

*/

[dawugui]

如果是 '07:46:00' 减 '16:41:00' 才等于 -08：55：00

[-狙击手-]

declare @dt1 as varchar(8)
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 行）


*/

[dawugui]

石头,这个结果不对.应该是-09:05:00才对.

[-狙击手-]

[Quote=引用 10 楼 dawugui 的回复:]
石头,这个结果不对.应该是-09:05:00才对.
[/Quote]

人家就是类似来个60进制的加减法

[cson_cson]

declare @dt1 datetime
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   

[dcren118]

贴出整个SQL语句 ！

Update a Set a.Reason = '迟到',a.[MorTime] = （要实现这个功能的部分其实根据字段EndOfVacation的值来判断如果值小于9：30：00 并且大于 08：30：00计算迟到时间 谢谢） FROM LeaveAll a join (Select EmpID,convert(varchar(21),EndOfVacation,108) as EndOfVacation From UserInfo Where convert(char(10),StartOFVacation,121) = @today and convert(varchar(21),EndOfVacation,108) <= '08:30:00') b
on a.UserID = b.EmpID and a.[DateTime] = @today

[meiZiNick]

没遇到过这种情况.