declare @t table(col1 varchar(30),col2 varchar(30))insert @t select '2007-04-02','2007-04-05'select datediff(day,cast(col1 as datetime),cast(col2 as datetime)) from @t
declare @e varchar(06), @s varchar(06) set @e='223000' set @s='213000' select datediff(minute,'2000-1-1'+' '+substring(@s,1,2)+':'+substring(@s,3,2)+':'+substring(@s,5,2),'2000-1-1'+' '+substring(@e,1,2)+':'+substring(@e,3,2)+':'+substring(@e,5,2))
declare @e varchar(06), @s varchar(06) set @e='223000' set @s='213000' select datediff(minute,'2000-1-1'+' '+substring(@s,1,2)+':'+substring(@s,3,2)+':'+substring(@s,5,2),'2000-1-1'+' '+substring(@e,1,2)+':'+substring(@e,3,2)+':'+substring(@e,5,2)) ----------- 60
set @e='223000'
set @s='213000'
select datediff(minute,'2000-1-1'+' '+substring(@s,1,2)+':'+substring(@s,3,2)+':'+substring(@s,5,2),'2000-1-1'+' '+substring(@e,1,2)+':'+substring(@e,3,2)+':'+substring(@e,5,2))
set @e='223000'
set @s='213000'
select datediff(minute,'2000-1-1'+' '+substring(@s,1,2)+':'+substring(@s,3,2)+':'+substring(@s,5,2),'2000-1-1'+' '+substring(@e,1,2)+':'+substring(@e,3,2)+':'+substring(@e,5,2))
-----------
60
Select @Time1 = '223549', @Time2 = '223849'
Select DateDiff(mi, Cast('1900-01-01 ' + Stuff(Stuff(@Time1, 3, 0, ':'), 6, 0 , ':') As DateTime), Cast('1900-01-01 ' + Stuff(Stuff(@Time2, 3, 0, ':'), 6, 0 , ':') As DateTime)) --Result
/*
3
*/
Select @Time1 = '213000', @Time2 = '223000'
Select DateDiff(mi, '1900-01-01 ' + Stuff(Stuff(@Time1, 3, 0, ':'), 6, 0 , ':'), '1900-01-01 ' + Stuff(Stuff(@Time2, 3, 0, ':'), 6, 0 , ':')) --Result
/*
60
*/
select @a=223000,@b=213000
select
datediff(n,
(left(@b,2)+':'+substring(rtrim(@b),3,2)+':'+right(@b,2)),
(left(@a,2)+':'+substring(rtrim(@a),3,2)+':'+right(@a,2))
)
-----------
60(所影响的行数为 1 行)