---兩個時間之差的合計DECLARE @I INTSET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())SELECT convert(varchar(10), @I/86400000) + ' Days ' + convert(varchar(10), (@I%86400000)/3600000) + ' Hours '+ convert(varchar(10), (@I%3600000)/60000) + ' Mins '+ convert(varchar(10), (@I%60000)/1000) + ' sec ' + convert(varchar(10), @I%1000) + ' ms ' AS [DD:HH:MM:SS:MS]-- Find Hours, Minutes and Seconds in between two datetimeDECLARE @First datetimeDECLARE @Second datetimeSET @First = '04/02/2008 05:23:22'SET @Second = getdate()SELECT DATEDIFF(day,@First,@Second)*24 as TotalHours,DATEDIFF(day,@First,@Second)*24*60 as TotalMinutes,DATEDIFF(day,@First,@Second)*24*60*60 as TotalSeconds select DateDiff ('d','1999-05-01','1999-03-07')/*--Geovin Du 塗聚文 締友計算機信息技術有限公司year yy, yyyy quarter qq, q Month mm, m dayofyear dy, y Day dd, d Week wk, ww Hour hh minute mi, n second ss, s millisecond ms */---分鍾化小時DECLARE @strardate datetime,@enddate datetime,@hour int,@minute floatset @strardate='2011-08-15 18:00'set @enddate='2011-08-15 22:45'select @hour=DateDiff(hour,@strardate,@enddate)select @minute=DateDiff(minute,@strardate,@enddate)%60select @hourselect @hour+@minute/60select @hour+round(@minute/60,1,1)---四舍五入SELECT ROUND(150.75, 0);GOSELECT ROUND(150.75, 0, 1);GOselect DateDiff(hh,@strardate,@enddate)select DateDiff(mi,@strardate,@enddate)declare @hms varchar(8)set @hms = '10:30:00'declare @hours intdeclare @minutes decimalset @hours = datepart(hour, @hms)set @minutes = datepart(minute, @hms)select @minutes/60+@hoursselect round(@minutes/60,1)+@hours---四舍五入---公司的加班計算，當大於半小時計加班半小時，不足半小時，不計加班DECLARE @strardate datetime,@enddate datetime,@hour int,@minute float,@minutes floatset @strardate='2011-08-15 18:00'set @enddate='2011-08-15 22:45'select @hour=DateDiff(hour,@strardate,@enddate)select @minute=DateDiff(minute,@strardate,@enddate)%60--select @hour+round(@minute/60,1,1)set @minutes=@minute/60if @minutes>0.5 set @minutes=0.5if @minutes<0.5 set @minutes=0select @minutesselect @hour--select @minuteselect @hour+@minutes---select sum(TotalSeconds) / 86400 as Days, (sum(TotalSeconds) % 86400) / 3600 as Hours, (sum(TotalSeconds) % 3600) / 60 as Minutes, sum(TotalSeconds) % 60 as Secondsfrom( select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds from Events) x
