sql 时间计算

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91

---兩個時間之差的合計 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