sql script: Calculating Days
1 --日 (sql server 2005) www.2cto.com
2
3 CREATE TABLE T1 (ID INTEGER)
4 INSERT INTO T1 VALUES (1)
5 ----
6 with x(dy,dm,mth,dw,wk)
7 as(
8 select dy,
9 day(dy) dm,
10 datepart(m,dy) mth,
11 datepart(dw,dy) dw,
12 case when datepart(dw,dy)=1 www.2cto.com
13 then datepart(ww,dy)-1
14 else datepart(ww,dy)
15 end wk
16 from(
17 select dateadd(day,-day(getdate())+1,getdate()) dy
18 from t1
19 ) x
20 union all
21 select dateadd(d,1,dy),day(dateadd(d,1,dy)),mth,
22 datepart(dw,dateadd(d,1,dy)),
23 case when datepart(dw,dateadd(d,1,dy))=1
24 then datepart(wk,dateadd(d,1,dy))-1
25 else datepart(wk,dateadd(d,1,dy))
26 end
27 from x
28 where datepart(m,dateadd(d,1,dy))=mth
29 )
30 select max(case dw when 2 then dm end) as Mo,
31 max(case dw when 3 then dm end) as Tu,
32 max(case dw when 4 then dm end) as We,
33 max(case dw when 5 then dm end) as Th,
34 max(case dw when 6 then dm end) as Fr,
35 max(case dw when 7 then dm end) as Sa,
36 max(case dw when 1 then dm end) as Su
37 from x
38 group by wk
39 order by wk
40
41 ---
42 select dy,
43 day(dy) dm,
44 datepart(m,dy) mth,
45 datepart(dw,dy) dw,
46 case when datepart(dw,dy)=1
47 then datepart(ww,dy)-1
48 else datepart(ww,dy)
49 end wk
50 from(
51 select dateadd(day,-day(getdate())+1,getdate()) dy
52 from t1
53 ) x
54 go
55
56 --第 周
57 with x(dy,dm,mth,dw,wk)
58 as(
59 select dy,
60 day(dy) dm,
61 datepart(m,dy) mth,
62 datepart(dw,dy) dw,
63 case when datepart(dw,dy)=1
64 then datepart(ww,dy)-1
65 else datepart(ww,dy)
66 end wk
67 from(
68 select dateadd(day,-day(getdate())+1,getdate()) dy
69 from t1
70 ) x
71 union all
72 select dateadd(d,1,dy), day(dateadd(d,1,dy)),mth,
73 datepart(dw,dateadd(d,1,dy)),
74 case when datepart(dw,dateadd(d,1,dy))=1
75 then datepart(wk,dateadd(d,1,dy))-1
76 else datepart(wk,dateadd(d,1,dy))
77 end
78 from x
79 where datepart(m,dateadd(d,1,dy))=mth
80 )
81 select * from x
1 ---
2 CREATE FUNCTION [dbo].[f_week_days_in_period] (@start_date datetime, @end_date datetime)
3 RETURNS INT
4 AS
5 BEGIN
6
7 -- If the start date is a weekend, move it foward to the next weekday
8 WHILE datepart(weekday, @start_date) in (1,7) -- Sunday, Saturday
9 BEGIN
10 SET @start_date = dateadd(d,1,@start_date)
11 END
12
13 -- If the end date is a weekend, move it back to the last weekday
14 WHILE datepart(weekday, @end_date) in (1,7) -- Sunday, Saturday
15 BEGIN
16 SET @end_date = dateadd(d,-1,@end_date)
17 END
18
19 -- Weekdays are total days in perion minus weekends. (2 days per weekend)
20 -- Extra weekend days were trimmed off the period above.
21 -- I am adding an extra day to the total to make it inclusive.
22 -- i.e. 1/1/2008 to 1/1/2008 is one day because it includes the 1st
23 RETURN (datediff(d,@start_date,@end_date) + 1) - (datediff(ww,@start_date,@end_date) * 2)
24
25 END
26
27 ---
28 select dbo.f_week_days_in_period('2013-02-01','2013