sql server:自定義計算固定工作日，雙休日函數

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97

---sql server declare @date datetime set @date='2012-02-03'--getdate() --本月第一天 SELECT DATEADD(mm, DATEDIFF(mm,0,@date), 0) --本月最后一天 SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@date)+1, 0))     ---有個月多少天函數 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAMonthHowNumber]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[GetAMonthHowNumber] GO CREATE   function  GetAMonthHowNumber (      @date datetime ) returns int as begin     declare @int int     select @int=datediff(dd , @date , dateadd(mm, 1, @date))     return @int end GO   -- select [dbo].[GetAMonthHowNumber] (getdate()) AS '月天數'     ---計算當月周六，周日有多少天 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAMonthStatSunNumber]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[GetAMonthStatSunNumber] GO CREATE   function  GetAMonthStatSunNumber (      @date datetime ) returns int as begin declare @Sdate datetime ,@Edate datetime   SELECT @Sdate=DATEADD(mm, DATEDIFF(mm,0,@date), 0) SELECT @Edate=dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@date)+1, 0)) --set @Sdate='2013-05-01' --set @Edate='2013-05-31' declare @aa table (strdate datetime)  declare @i int  set @i=datediff(day,@Sdate,@Edate)  while(@i>=0)  begin  insert @aa  values (dateadd(day,@i,@Sdate))  set @i=@i-1  end  select @i= count(*)  from @aa where   datepart(weekday,strdate) in (1,7)  --not in (1,7) return @i end go   -- select [dbo].[GetAMonthStatSunNumber] (getdate()) AS '雙休日天數'       --計算當月除周六，周日有多少天，也是有多少工作日 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAMonthWorkDayNumber]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[GetAMonthWorkDayNumber] GO CREATE   function  GetAMonthWorkDayNumber (      @date datetime ) returns int as begin declare @Sdate datetime ,@Edate datetime   SELECT @Sdate=DATEADD(mm, DATEDIFF(mm,0,@date), 0) SELECT @Edate=dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@date)+1, 0)) --set @Sdate='2013-05-01' --set @Edate='2013-05-31' declare @aa table (strdate datetime)  declare @i int  set @i=datediff(day,@Sdate,@Edate)  while(@i>=0)  begin  insert @aa  values (dateadd(day,@i,@Sdate))  set @i=@i-1  end  select @i= count(*)  from @aa where   datepart(weekday,strdate) not in (1,7)  --not in (1,7) return @i end go   --- select [dbo].[GetAMonthWorkDayNumber] (getdate()) as '工作日天數'