1 --日曆(sql server 2005) 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 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-02-28')