当前位置：首页 > news >正文

Sql: 請假跨月份問題，或跨年份問題日期部分边界

news 来源：原创 2024/5/19 2:08:35

 1 ---請假跨月份問題，或跨年份問題 日期部分边界
 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