算出一年中有几个周日,或是有几周

[xikboy]

RT

[xikboy]

以周一为第一天。

[hery2002]

[Quote=引用 2 楼 liangCK 的回复:]
2000用临时表.
[/Quote]

[xikboy]

不明白。

[npkaida]

select datepart(week,'2008-12-31')

[liangCK]

--2005
;with cte as
(
  select dt=cast(convert(varchar(5),getdate(),120)+'1-1' as datetime),[year]=year(getdate())
  union all
  select dateadd(day,1,c.[dt]),c.[year]
  from cte c
  where year(dateadd(day,1,c.[dt]))=c.[year]
)
select count(*) cnt
from cte
where (datepart(weekday,dt)+@@datefirst-1)=7
option (maxrecursion 0)

--2000
select top 366 id=identity(int,1,1) into # from syscolumns,sysobjects

select count(*) cnt
from
(
   select dateadd(day,id,convert(varchar(5),getdate(),120)+'1-1') dt
   from #
   where dateadd(day,id,convert(varchar(5),getdate(),120)+'1-1')<=convert(varchar(5),getdate(),120)+'12-31'
) t
where (datepart(weekday,dt)+@@datefirst-1)=7

[xikboy]

我要以周日为最后一天，

如1月6号是第一周，而不是第二周

[liangCK]

set datefirst 7

[liangCK]

--2005

set datefirst 7

;with cte as
(
  select dt=cast(convert(varchar(5),getdate(),120)+'1-1' as datetime),[year]=year(getdate())
  union all
  select dateadd(day,1,c.[dt]),c.[year]
  from cte c
  where year(dateadd(day,1,c.[dt]))=c.[year]
)
select count(*) cnt
from cte
where (datepart(weekday,dt)+@@datefirst-1)=7
option (maxrecursion 0)

--2000
select top 366 id=identity(int,1,1) into # from syscolumns,sysobjects

select count(*) cnt
from
(
   select dateadd(day,id,convert(varchar(5),getdate(),120)+'1-1') dt
   from #
   where dateadd(day,id,convert(varchar(5),getdate(),120)+'1-1')<=convert(varchar(5),getdate(),120)+'12-31'
) t
where (datepart(weekday,dt)+@@datefirst-1)=7

drop table #

[liangCK]

应该是
set datefirst 1

[liangCK]

--2005

set datefirst 1

;with cte as
(
  select dt=cast(convert(varchar(5),getdate(),120)+'1-1' as datetime),[year]=year(getdate())
  union all
  select dateadd(day,1,c.[dt]),c.[year]
  from cte c
  where year(dateadd(day,1,c.[dt]))=c.[year]
)
select count(*) cnt
from cte
where (datepart(weekday,dt)+@@datefirst-1)=7
option (maxrecursion 0)

--2000
select top 366 id=identity(int,1,1) into # from syscolumns,sysobjects

select count(*) cnt
from
(
   select dateadd(day,id,convert(varchar(5),getdate(),120)+'1-1') dt
   from #
   where dateadd(day,id,convert(varchar(5),getdate(),120)+'1-1')<=convert(varchar(5),getdate(),120)+'12-31'
) t
where (datepart(weekday,dt)+@@datefirst-1)=7

drop table #


/*
cnt
-----------
52

(1 行受影响)
*/

[ivan_ren]

create function dbo.get_weekday_count_f(@y integer)
returns integer
as
begin
declare @d datetime
declare @c integer
declare @t_y integer
set @t_y=@y+1
set @c=0
set @d=convert(varchar(4),@y)+'-01'+'-01'

while @y<@t_y
begin
if datepart(weekday,@d)=6
begin
set @c=@c+1
set @d=dateadd(day,1,@d)
set @y=datepart(year,@d)
end
else
begin
set @d=dateadd(day,1,@d)
set @y=datepart(year,@d)
end

end

return @c

end

select dbo.get_weekday_count_f(2006)

[ekin529818]

[Quote=引用 4 楼 xikboy 的回复:]
以周一为第一天。
[/Quote]
不是以周日为第一天的么?如果是这样,那么怎么实现?

[184270428]

declare @i int,@st_date datetime,@en_date datetime
set @i = 5 --星期天为0
set @st_date = cast(cast(year(getdate()) as varchar(10)) + '-1-1' as datetime)
set @en_date = dateadd(d,-1,cast(cast(year(getdate()) + 1 as varchar(10)) + '-1-1' as datetime))

set @st_date = dateadd(d,(@i - datepart(w,@st_date) + 8) % 7 - 1,@st_date)
set @en_date = dateadd(d,case when datepart(w,@en_date) < @i then -7 else 0 end + @i - datepart(w,dateadd(ww,-1,@en_date)) ,@en_date)

select datediff(ww,@st_date,@en_date)

[184270428]

declare @i int,@st_date datetime,@en_date datetime
set @i = 5 --星期天为0,可任意改变这里.
set @st_date = cast(cast(year(getdate()) as varchar(10)) + '-1-1' as datetime)--当前年的第一天
set @en_date = dateadd(d,-1,cast(cast(year(getdate()) + 1 as varchar(10)) + '-1-1' as datetime))--当前年的最后一天

set @st_date = dateadd(d, (@i - datepart(w,@st_date) + 6) % 7 + 1,@st_date)--第一天后每一个同@i同星期的日期
set @en_date = dateadd(d,case when datepart(w,@en_date) < @i then -7 else 0 end + @i - datepart(w,dateadd(ww,-1,@en_date)) ,@en_date)--最后一天前,同@i同星期的日期

select datediff(ww,@st_date,@en_date)

前面错了

[184270428]

偶的代码2000 2005 都可以,copy到查询分析器里就可以执行了.

[npkaida]

set datefirst 1 -- 设周一为第一天
select datepart(week,'2008-1-6')

[pt1314917]

create proc p_count
@year int
as
set datefirst 1
select top 366 id=identity(int,0,1) into # from syscolumns
select 周日个数=count(1) from #
where datepart(dw,dateadd(dd,id,ltrim(@year)+'-1-1'))=7
and dateadd(dd,id,ltrim(@year)+'-1-1')<ltrim(@year+1)+'-1-1'
go

--得出2008年有多少个周日。
exec p_count 2008 

[ChinaJiaBing]

declare @a datetime
set @a = '2008-1-1'

if object_id('tb_tmp') is not null
   drop table tb_tmp
create table tb_tmp
( y datetime )
while year(@a)= '2008'
begin  
 insert into tb_tmp values (@a)
    set @a=@a + 1
end
select count(*) from tb_tmp where datepart(dw,y)=6

[hongtiancai]

学习

[gs2006]

这里的高手真多啊！学习中。