---兩個時間之差的合計 DECLARE @I INT SET @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 datetime DECLARE @First datetime DECLARE @Second datetime SET @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 float set @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 select @hour+@minute/60 select @hour+round(@minute/60,1,1)---四舍五入 SELECT ROUND(150.75, 0); GO SELECT ROUND(150.75, 0, 1); GO select DateDiff(hh,@strardate,@enddate) select DateDiff(mi,@strardate,@enddate) declare @hms varchar(8) set @hms = '10:30:00' declare @hours int declare @minutes decimal set @hours = datepart(hour, @hms) set @minutes = datepart(minute, @hms) select @minutes/60+@hours select round(@minutes/60,1)+@hours---四舍五入 ---公司的加班計算,當大於半小時計加班半小時,不足半小時,不計加班 DECLARE @strardate datetime,@enddate datetime,@hour int,@minute float,@minutes float set @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/60 if @minutes>0.5 set @minutes=0.5 if @minutes<0.5 set @minutes=0 select @minutes select @hour --select @minute select @hour+@minutes --- select sum(TotalSeconds) / 86400 as Days, (sum(TotalSeconds) % 86400) / 3600 as Hours, (sum(TotalSeconds) % 3600) / 60 as Minutes, sum(TotalSeconds) % 60 as Seconds from ( select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds from Events ) x
sql 时间计算
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)成功.---Geovin Du(涂聚文)