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

baacloud免费翻墙vpn注册使用
---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 '工作日天數'
赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏