--星期天天數 declare @t table (ym char(7)) insert into @t select '2008-06' union select '2008-07'select ym, ceiling((datediff(day,dt_b,dateadd(month,1,dt_b))-((8-datepart(dw,dt_b))%7)) /7.0) from (select ym,cast(ym+'-01' as datetime) as dt_b from @t) a /*(影響 2 個資料列)ym ------- ------------------- 2008-06 5 2008-07 4(影響 2 個資料列)*/
datepart(weekday , dt) in (1,7)
declare @t table(dt datetime) insert into @t select '2008-10-10' insert into @t select '2008-10-12' insert into @t select '2008-10-13' insert into @t select '2008-10-14' insert into @t select '2008-10-15' insert into @t select '2008-10-16' insert into @t select '2008-10-17' insert into @t select '2008-10-18' insert into @t select '2008-10-19' insert into @t select '2008-10-26' insert into @t select '2008-10-27' insert into @t select '2008-10-21' declare @s varchar(10) set @s='2008-10' set datefirst 1 select count(1) from @t where datediff(mm,@s+'-01',dt)=0 and datepart(dw,dt)>=6
declare @date smalldatetime,@startdate smalldatetime declare @tb table(date smalldatetime)set @date='2008-10-24' set @startdate=cast(left(convert(varchar(10),@date,120),8)+'01' as smalldatetime)while datediff(mm,@startdate,@date)=0 begin insert @tb select @startdate set @startdate=dateadd(day,1,@startdate) endset DATEFIRST 1 select *,datename(dw,date) as weeks from @tb where datepart(dw,date) in (6,7) /* date weeks ------------------------------------------------------ ------------------------------ 2008-10-04 00:00:00 Saturday 2008-10-05 00:00:00 Sunday 2008-10-11 00:00:00 Saturday 2008-10-12 00:00:00 Sunday 2008-10-18 00:00:00 Saturday 2008-10-19 00:00:00 Sunday 2008-10-25 00:00:00 Saturday 2008-10-26 00:00:00 Sunday*/
create FUNCTION [dbo].[fn_get_weekends] (@year nvarchar(4),@month nvarchar(2)) RETURNS int AS BEGIN declare @rate lint select @rate=(select count(*) from tb where datediff(month,cast((@year+'-'+@month) as datetime)),col_date)=0 and datepart(wd,col_date)=0 or datepart(wd,col_date)=1) return @rate END
declare @dt smalldatetime set @dt='2008-06-06 06:06:06' --set @dt=getdate()select date=convert(varchar(7),@dt,120),sumofweekenddays=count(*) from ( select top(day(dateadd(d,-1,convert(varchar(8),dateadd(m,1,@dt),120)+'1'))) date=convert(varchar(10),dateadd(d,row_number() over(order by id),dateadd(d,-1,convert(varchar(8),@dt,120)+'1')),120) from sysobjects ) a where datepart(dw,date) in (1,7)/* date sumofweekenddays ------- ---------------- 2008-06 9(1 row(s) affected)*/
之前写错了create FUNCTION [dbo].[fn_get_dw_num] (@year nvarchar(4),@month nvarchar(2)) --@year和@month是传入的要查询的年、月参数 RETURNS int AS BEGIN declare @rate lint select @rate=(select count(*) from tb where datediff(month,cast((@year+'-'+@month) as datetime)),col_date)=0 and datepart(dw,col_date) in(1,7)) return @rate END
declare @t table (ym char(7))
insert into @t
select '2008-06' union
select '2008-07'select ym,
ceiling((datediff(day,dt_b,dateadd(month,1,dt_b))-((8-datepart(dw,dt_b))%7)) /7.0)
from (select ym,cast(ym+'-01' as datetime) as dt_b from @t) a
/*(影響 2 個資料列)ym
------- -------------------
2008-06 5
2008-07 4(影響 2 個資料列)*/
insert into @t select '2008-10-10'
insert into @t select '2008-10-12'
insert into @t select '2008-10-13'
insert into @t select '2008-10-14'
insert into @t select '2008-10-15'
insert into @t select '2008-10-16'
insert into @t select '2008-10-17'
insert into @t select '2008-10-18'
insert into @t select '2008-10-19'
insert into @t select '2008-10-26'
insert into @t select '2008-10-27'
insert into @t select '2008-10-21'
declare @s varchar(10)
set @s='2008-10'
set datefirst 1
select count(1) from @t where datediff(mm,@s+'-01',dt)=0 and datepart(dw,dt)>=6
declare @date smalldatetime,@startdate smalldatetime
declare @tb table(date smalldatetime)set @date='2008-10-24'
set @startdate=cast(left(convert(varchar(10),@date,120),8)+'01' as smalldatetime)while datediff(mm,@startdate,@date)=0
begin
insert @tb select @startdate
set @startdate=dateadd(day,1,@startdate)
endset DATEFIRST 1
select *,datename(dw,date) as weeks from @tb where datepart(dw,date) in (6,7)
/*
date weeks
------------------------------------------------------ ------------------------------
2008-10-04 00:00:00 Saturday
2008-10-05 00:00:00 Sunday
2008-10-11 00:00:00 Saturday
2008-10-12 00:00:00 Sunday
2008-10-18 00:00:00 Saturday
2008-10-19 00:00:00 Sunday
2008-10-25 00:00:00 Saturday
2008-10-26 00:00:00 Sunday*/
create FUNCTION [dbo].[fn_get_weekends] (@year nvarchar(4),@month nvarchar(2))
RETURNS int AS
BEGIN
declare @rate lint
select @rate=(select count(*) from tb where datediff(month,cast((@year+'-'+@month) as datetime)),col_date)=0 and datepart(wd,col_date)=0 or datepart(wd,col_date)=1)
return @rate
END
set @dt='2008-06-06 06:06:06'
--set @dt=getdate()select date=convert(varchar(7),@dt,120),sumofweekenddays=count(*) from
(
select top(day(dateadd(d,-1,convert(varchar(8),dateadd(m,1,@dt),120)+'1')))
date=convert(varchar(10),dateadd(d,row_number() over(order by id),dateadd(d,-1,convert(varchar(8),@dt,120)+'1')),120)
from sysobjects
) a
where datepart(dw,date) in (1,7)/*
date sumofweekenddays
------- ----------------
2008-06 9(1 row(s) affected)*/
RETURNS int AS
BEGIN
declare @rate lint
select @rate=(select count(*) from tb where datediff(month,cast((@year+'-'+@month) as datetime)),col_date)=0 and datepart(dw,col_date) in(1,7))
return @rate
END