Sql:跨月份问题或跨年份问题日期部分边界

2 declare @sart datetime,@end datetime,@d int,@s1 datetime,@e1 datetime,@s2 datetime,@e2 datetime,@t varchar(50),@t2 varchar(50),@I int

3 set @sart='2012-11-18 14:56:12'

4 set @end='2012-12-08 14:56:12'

5 --set @I=DATEDIFF(ms,@sart,@end)

6 set @I=DATEDIFF(ss,@sart,@end)

7 if(year(@sart)=year(@end) and month(@sart)=month(@end))

8 begin

9 set @d=day(@end)-day(@sart)+1

10 --select @d=datediff(day,@sart,@end)

11 --set @d=@d*8 AS '小 '

12 --select @d=datediff(hour,@sart,@end) AS '小 '

13 --select @d=@I/86400000

14 select @d=@I/86400

15 end

16 else

17 begin

18 if(year(@sart)=year(@end) and month(@end)>month(@sart))

19 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)

20 select @s1=cast( @t as datetime)

21 --月末

22 select @e1=DATEADD(day, -1, DATEADD(month, 1,DATEADD(day,-DATEPART(day, @sart)+1,@sart )))

23 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)

24 select @e2=cast( @t2 as datetime)

25 --月初

26 select @s2=DATEADD(month, 0,DATEADD(day,-DATEPART(day, @end)+1,@end))

27 --select @d=@I/86400000

28 select @d=@I/86400

29 end

30 select @I,@d,@s1,@e1,@s2,@e2

31

32

33

34

35

36 ---

37 DECLARE @I INT

38 SET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())

39

40 SELECT

41 convert(varchar(10), (@I/86400000)) + ' Days ' +

42 convert(varchar(10), ((@I%86400000)/3600000)) + ' Hours '+

43 convert(varchar(10), (((@I%86400000)%3600000)/60000)) + ' Mins '+

44 convert(varchar(10), ((((@I%86400000)%3600000)%60000)/1000)) + ' sec ' +

45 convert(varchar(10), (((@I%86400000)%3600000)%1000)) + ' ms ' AS [DD:HH:MM:SS:MS]

46 --

47 select

48 *,

49 Days = datediff(dd,0,DateDif),

50 Hours = datepart(hour,DateDif),

51 Minutes = datepart(minute,DateDif),

52 Seconds = datepart(second,DateDif),

53 MS = datepart(ms,DateDif)

54 from

55 (

56 select

57 DateDif = EndDate-StartDate,

58 aa.*

59 from

60 ( -- Test Data

61 Select

62 StartDate = convert(datetime,'20090213 02:44:37.923'),

63 EndDate = convert(datetime,'20090715 13:24:45.837')

64 ) aa

65 ) a