如何根據月份計算出該月份的星期日的天數？

晕..循环当月每一天..datepart(weekday,)去判断就是了.

--> liangCK小梁于2008-07-22
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (月份 varchar(7))
insert into #T
select '2008-06' union all
select '2008-07';with cte as
(
  select 日期=cast(月份+'-1' as datetime),月=month(cast(月份+'-1' as datetime)) from #T
  union all
  select dateadd(day,1,c.日期),c.月
  from cte c where month(dateadd(day,1,c.日期))=c.月
)
select convert(varchar(7),日期,120) 月份,count(*) cnt
from cte
where datepart(weekday,日期)=7
group by convert(varchar(7),日期,120)/*
月份      cnt
------- -----------
2008-06 4
2008-07 4(2 行受影响)
*/

“/”应用程序中的服务器错误。
--------------------------------------------------------------------------------由于目标机器积极拒绝，无法连接。 192.168.1.242:8086
说明: 执行当前 Web 请求期间，出现未处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。异常详细信息: System.Net.Sockets.SocketException: 由于目标机器积极拒绝，无法连接。 192.168.1.242:8086源错误: 执行当前 Web 请求期间生成了未处理的异常。可以使用下面的异常堆栈跟踪信息确定有关异常原因和发生位置的信息。  堆栈跟踪:
[SocketException (0x274d): 由于目标机器积极拒绝，无法连接。 192.168.1.242:8086]
   System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) +2668969
   System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) +717
   CSDN.Community.TopicFileDataCenter.TopicFileComponent.ReCreateTopic(ReplyGenerateData[] rgds, TopicGenerateData tgd) +0
   CSDN.Community.TopicFileDataCenter.TopicFileDataCenterEntry.ReCreateTopic(TopicInfo topic, CommunityUser postUser, UserSectionProfile usp, ReplyInfo[] replies, CommunityUserCollection replyUsers, UserSectionProfileCollection replyUserProfiles) +598
   CSDN.Community.PointForum.Services.BuildTopicManager.BuildTopic(ConfigInfo Forum, Guid topicId, String& topicUrl) +918
   CSDN.Community.PointForum.WebControls.CreateTopicFilePage.OnLoad(EventArgs e) +704
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436已經積極拒絕我N次了，這次能不拒絕我麽？我就想看看回復！

--> liangCK小梁于2008-07-22
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (月份 varchar(7))
insert into #T
select '2008-06' union all
select '2008-07';with cte as
(
  select 日期=cast(月份+'-1' as datetime),月=month(cast(月份+'-1' as datetime)) from #T
  union all
  select dateadd(day,1,c.日期),c.月
  from cte c where month(dateadd(day,1,c.日期))=c.月
)
select convert(varchar(7),日期,120) 月份,
       count(case when datepart(weekday,日期)=1 then 1 end) cnt
from cte
group by convert(varchar(7),日期,120)
/*
月份      cnt
------- -----------
2008-06 5
2008-07 4
*/

使用@@datefirst可以解决一个星期是从星期一,星期天开始..

if object_id('tb') is not null
drop table tb
go
select top 365 '2008-01-01' as date,id=identity(int,0,1) into tb from sysobjects aselect convert(varchar(10),dateadd(dd,id,date),120) as 时间,
'星期天' as 星期天
from tb
where dateadd(dd,id,date) between '2008-06-01' and '2008-08-01'
and (datepart(dw,dateadd(dd,id,date))+@@datefirst-1)%7=0时间星期天
2008-06-01 星期天
2008-06-08 星期天
2008-06-15 星期天
2008-06-22 星期天
2008-06-29 星期天
2008-07-06 星期天
2008-07-13 星期天
2008-07-20 星期天
2008-07-27 星期天

--> liangCK小梁于2008-07-22
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (月份 varchar(7))
insert into #T
select '2008-06' union all
select '2008-07';with cte as
(
  select 日期=cast(月份+'-1' as datetime),月=month(cast(月份+'-1' as datetime)) from #T
  union all
  select dateadd(day,1,c.日期),c.月
  from cte c where month(dateadd(day,1,c.日期))=c.月
)
select convert(varchar(7),日期,120) 月份,
       count(case when (datepart(weekday,日期)+@@datefirst-1)%7=0 then 1 end) cnt
from cte
group by convert(varchar(7),日期,120)
/*
月份      cnt
------- -----------
2008-06 5
2008-07 4
*/

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
/*
ym
------- ---------------------------------------
2008-06 5
2008-07 4(2 行受影响)
*/

if object_id('tb') is not null
drop table tb
go
select top 365 '2008-01-01' as date,id=identity(int,0,1) into tb from sysobjects aselect convert(varchar(7),dateadd(dd,id,date),120) as 时间,
count(1) as 星期天
from tb
where dateadd(dd,id,date) between '2008-06-01' and '2008-08-01'
and (datepart(dw,dateadd(dd,id,date))+@@datefirst-1)%7=0
group by convert(varchar(7),dateadd(dd,id,date),120)时间星期天
2008-06 5
2008-07 4

create function dbo.get_count_sunday(@d datetime)
returns integer
begin
declare @d_i datetime
declare @is_weekday datetime
declare @Y as integer
declare @m as integer
declare @t_d as integer
declare @c as integer
declare @i as integer
set @t_d=28
set @c=0
--Ê×ÏÈÅÐ¶ÏÊÇ·ñÎªÈòÄê
if @d is null
  set @c=0
   else
   select @d_i=@d
select @y=year(@d_i)
select @m=month(@d_i)
if @y%4=0 and @y%100<>0
   set @t_d=29
if  (@m=1 or @m=3 or @m=5 or @m=7 or @m=8 or @m=10 or @m=12)
set @t_d=31
ELSE if (@m=4 or @m=6 or @m=9 or @m=11)
set @t_d=30

if @t_d=0
  begin
    set @c=0
  end
  set @i=1

while @i<=@t_d
begin
select @is_weekday=convert(varchar(4),@y)+'-'+convert(varchar(2),@m)+'-'+convert(varchar(2),@i)

if datepart(weekday,@is_weekday)=1
  begin
  set @c=@c+1
  end
set @i=@i+1
endreturn @c
endselect dbo.get_count_sunday(getdate())

NND，晚上終于不被CSDN積極拒絕了！被拒絕了一下午，都看不到回復！
20樓兄弟100分，另外100酌情分給其他兄弟！

10楼这样调用也方便..只是where改为你的条件就行.

declare @weekday table
(a nvarchar(8))
insert into @weekday select '2008-06'
union all select '2008-07'

select a ,ceiling (datediff(day,b, dateadd(month,1,b))/7.0 )星期天天数
from (select a,cast(a+'-1' as datetime) as b from @weekday b ) c

select ((datepart(day,dateadd(day,-1, convert(char(8), DATEADD(Month,1,GETDATE()),120)+'1')))
-(datepart(weekday,dateadd(day,-1, convert(char(8), DATEADD(Month,1,GETDATE()),120)+'1')))
-(7-datepart(weekday,month(getdate())+'01')+1))/7+1

Declare
@Month1 nchar(7),
@Month2 nchar(7)
Select @Month1='2008-06',@Month2='2008-07'
Select
[月份]=Convert(char(7),date,120),
[星期日天數]=CEILING((Datediff(day,date,Dateadd(Month,1,date))+Datediff(day,-1,date)%7)/7.0)-Sign(Datediff(day,-1,date)%7)
From
(Select date=Convert(datetime,@Month1+'-01') Union All Select date=Convert(datetime,@Month2+'-01')) a

/*
月份星期日天數
------- ---------------------------------------
2008-06 5
2008-07 4
*/

创建一函数实现
--月份的天数28,29,30,31四种
--求出月份第一个星期天的日期,+28>当月的天数的为4,<=当月的天数的为5create  function dbo.getmonthsundays
(@month varchar(8))
returns int
as
begin

declare @sundays int
if  datediff(dd,convert(datetime,@month+'-1',120),dateadd(mm,1,convert(datetime,@month+'-1',120))) --指定月份天数
>=
   case when datepart(dw,convert(datetime,@month+'-1',120))=1 then 1 else 9-datepart(dw,convert(datetime,@month+'-1',120)) end +28 --第一个星期天+28
       set @sundays=5
else
   set @sundays=4
return @sundays
endselect dbo.getmonthsundays('2008-8')结果
-----------
5测试
declare @tmp table(fmonth varchar(8))
insert into @tmp
select '2008-01'
union select '2008-02'
union select '2008-03'
union select '2008-04'
union select '2008-05'
union select '2008-06'
union select '2008-07'
union select '2008-08'
union select '2008-09'
union select '2008-10'
union select '2008-11'
union select '2008-12'
union select '2009-01'select fmonth,dbo.getmonthsundays(fmonth) as sundays from @tmp结果
fmonth   sundays
-------- -----------
2008-01  4
2008-02  4
2008-03  5
2008-04  4
2008-05  4
2008-06  5
2008-07  4
2008-08  5
2008-09  4
2008-10  4
2008-11  5
2008-12  4
2009-01  4（所影响的行数为 13 行）

--把自己那天下午寫的也貼出來，方便以後的人查詢的時候多個參考
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE FUNCTION [dbo].[SundaysOfMonth]
(@Month varchar(7))RETURNS int
AS
BEGINDECLARE @SundaysOfMonth int
DECLARE @DaysOfMonth int
DECLARE @FirstDateOfMonth datetimeIF @Month IS NULL
   SET @FirstDateOfMonth=CAST(CONVERT(varchar(7),GETDATE(),120)+'-01' AS datetime)
ELSE
   SET @FirstDateOfMonth=CAST(@Month+'-01' AS datetime)SET @DaysOfMonth=DATEDIFF(dd,@FirstDateOfMonth,DATEADD(mm,1,@FirstDateOfMonth))

IF (@DaysOfMonth=29 AND DATEPART(dw,@FirstDateOfMonth)=1) OR
   (@DaysOfMonth=30 AND DATEPART(dw,@FirstDateOfMonth)IN (1,7)) OR
   (@DaysOfMonth=31 AND DATEPART(dw,@FirstDateOfMonth)IN (1,6,7))
SET @SundaysOfMonth=5
ELSE SET @SundaysOfMonth=4RETURN(@SundaysOfMonth)END

调试易

如何根據月份計算出該月份的星期日的天數？

解决方案 »