---請假跨月份問題，或跨年份問題 日期部分边界declare @sart datetime,@end datetime,@d int,@s1 datetime,@e1 datetime,@s2 datetime,@e2 datetime,@t varchar(50),@t2 varchar(50),@I intset @sart='2012-11-18 14:56:12'set @end='2012-12-08 14:56:12'--set @I=DATEDIFF(ms,@sart,@end)set @I=DATEDIFF(ss,@sart,@end)if(year(@sart)=year(@end) and month(@sart)=month(@end))beginset @d=day(@end)-day(@sart)+1--select @d=datediff(day,@sart,@end) --set @d=@d*8 AS '小時' --select @d=datediff(hour,@sart,@end) AS '小時' --select @d=@I/86400000select @d=@I/86400endelsebeginif(year(@sart)=year(@end) and month(@end)>month(@sart)) set @t=cast(year(@sart) as varchar)+'-'+cast(month(@sart) as varchar)+'-'+cast(day(@sart) as varchar)+' '+cast(DATEPART(hour,@sart) as varchar)+':'+cast(DATEPART(minute,@sart) as varchar)+':'+cast(DATEPART(second,@sart) as varchar) select @s1=cast( @t as datetime)--月末 select @e1=DATEADD(day, -1, DATEADD(month, 1,DATEADD(day,-DATEPART(day, @sart)+1,@sart )))set @t2=cast(year(@end) as varchar)+'-'+cast(month(@end) as varchar)+'-'+cast(day(@end) as varchar)+' '+cast(DATEPART(hour,@end) as varchar)+':'+cast(DATEPART(minute,@end) as varchar)+':'+cast(DATEPART(second,@end) as varchar)select @e2=cast( @t2 as datetime)--月初select @s2=DATEADD(month, 0,DATEADD(day,-DATEPART(day, @end)+1,@end))--select @d=@I/86400000select @d=@I/86400endselect @I,@d,@s1,@e1,@s2,@e2---DECLARE @I INTSET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())SELECTconvert(varchar(10), (@I/86400000)) + ' Days ' + convert(varchar(10), ((@I%86400000)/3600000)) + ' Hours '+convert(varchar(10), (((@I%86400000)%3600000)/60000)) + ' Mins '+convert(varchar(10), ((((@I%86400000)%3600000)%60000)/1000)) + ' sec ' +convert(varchar(10), (((@I%86400000)%3600000)%1000)) + ' ms ' AS [DD:HH:MM:SS:MS]--select *, Days = datediff(dd,0,DateDif), Hours = datepart(hour,DateDif), Minutes = datepart(minute,DateDif), Seconds = datepart(second,DateDif), MS = datepart(ms,DateDif)from ( select DateDif = EndDate-StartDate, aa.* from ( -- Test Data Select StartDate = convert(datetime,'20090213 02:44:37.923'), EndDate = convert(datetime,'20090715 13:24:45.837') ) aa ) a--
