欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-04-06 12:09:56
...

---sql server declare @date datetimeset @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 * fro

---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 '工作日天數' ,虚拟主机