SOS 100分!
根据进厂日期，比如2006/06/13进厂，算出06/13到06/30之间，有几个星期六，
就是从进厂的日期开始到这月结束，有多少个星期6
在线等，很急!
[解决办法]
declare @dtBegin datetime
declare @dtEnd datetime
declare @t table(dt datetime,name varchar(20))
set datefirst 1
set @dtBegin= '2006/06/13 '
set @dtEnd=getdate()
while @dtBegin <@dtEnd
begin
if datepart(weekday,@dtBegin)=6
begin
insert @t select @dtBegin, '星期六 '
end
set @dtBegin=@dtBegin+1
end
select count(*) from @t
[解决办法]
declare @dtBegin datetime
declare @dtEnd datetime
declare @t table(dt datetime,name varchar(20))
set datefirst 1
set @dtBegin= '2006/06/13 '
set @dtEnd=getdate()
while @dtBegin <@dtEnd
begin
if datepart(weekday,@dtBegin)=6
begin
insert @t select @dtBegin, '星期六 '
end
set @dtBegin=@dtBegin+1
end
select count(*) from @t
[解决办法]
if object_id( 'tempdb..#tmp ') is not null
drop table #tmp
set datefirst 1
GO
select top 30 id = identity(int,1,1) into #tmp from syscolumns,sysobjects
declare @Begin datetime,@End datetime
set @Begin = '2006/06/13 '
set @End = '2006/06/30 '
select dateadd(day,id,@Begin) from #tmp where
dateadd(day,id,@Begin) < @End and datepart(weekday,dateadd(day,id,@Begin)) = 6
drop table #tmp
/*结果
2006-06-17 00:00:00.000
2006-06-24 00:00:00.000
*/
[解决办法]
declare @s smalldatetime
set @s= '2007-01-03 '
select top 31 id=identity(int,0,1) into # from syscolumns
select count(1) a from
(select id,@s a,dateadd(weekday,id,@s) b,datename(weekday,dateadd(weekday,id,@s)) c from # a where dateadd(day,id,@s) <=cast(convert(varchar(8),dateadd(mm,1,@s),120)+ '01 ' as smalldatetime)) xx
where c= '星期六 '
drop table #
[解决办法]
declare @a datetime
set @a= '2006/06/13 '
declare @b datetime
set @b= '2006/06/30 '
declare @i int
set @i=0
while @a <@b
begin
if datepart(dw,@a)=7
begin
set @i=@i+1
end
set @a=dateadd(d,1,@A)
end
select @i
[解决办法]
-----------
2
(1 row(s) affected)
[解决办法]
如果你只要天数，我觉得以下的存储过程比较好：
create proc getSaturdayCount(
@dt datetime
)as
begin
declare @lastdateOfMonth datetime
--找出本月的最后一天
set @lastdateOfMonth=dateadd(day,-1,convert(char(8),dateadd(month,1,@dt),120)+ '1 ')
--本月的最后一天最后一天减去最近的一个星期六，得出最近的周六到月末的天数，再除以7，得有几个星期六
return (datediff(dd,@dt,@lastdateOfMonth)-(7-datepart(weekday,@dt)))/7
end
declare @r int
exec @r=getSaturdayCount '2006-6-13 '
print @r
[解决办法]
修改一下，忘了加上最近的一个星期六
------------------------------------------
create proc getSaturdayCount(
@dt datetime
)as
begin
declare @lastdateOfMonth datetime
--找出本月的最后一天
set @lastdateOfMonth=dateadd(day,-1,convert(char(8),dateadd(month,1,@dt),120)+ '1 ')
--本月的最后一天最后一天减去最近的一个星期六，得出最近的周六到月末的天数，再除以7，得有几个星期六
return (datediff(dd,@dt,@lastdateOfMonth)-(7-datepart(weekday,@dt)))/7+1
end
[解决办法]
create table #T (dtime datetime,[weekday] varchar(100))
declare @startDtime datetime
declare @endDtime datetime
set @startDtime = '2006/06/13 '
set @endDtime = '2006/06/30 '
select datepart(dw,@startDtime)
while (@startDtime <= @endDtime)
begin
if(datepart(dw,@startDtime) = 6)
begin
insert into #T select @startDtime , 'Saturday '
end
select @startDtime =@startDtime+1
print @startDtime
end
select * from #T
drop table #T
----------------------------
2006-06-16 00:00:00.000Saturday
2006-06-23 00:00:00.000Saturday
2006-06-30 00:00:00.000Saturday
[解决办法]
create table #T (dtime datetime,[weekday] varchar(100))
declare @startDtime datetime
declare @endDtime datetime
set @startDtime = '2006/06/13 '
set @endDtime = '2006/06/30 '
select datepart(dw,@startDtime)
while (@startDtime <= @endDtime)
begin
if(datepart(dw,@startDtime) = 7)
begin
insert into #T select @startDtime , 'Saturday '
end
select @startDtime =@startDtime+1
print @startDtime
end
select * from #T
drop table #T
[解决办法]
2006-06-17 00:00:00.000Saturday
2006-06-24 00:00:00.000Saturday
[解决办法]
declare @b datetime,@e datetime
select @b = '20060613 ',@e = getdate()
select datediff(day,@b,@e)/7+
case when datepart(dw,dateadd(wk,datediff(wk,@b,@e),@b)) < datepart(dw,@e)
and datepart(dw,dateadd(wk,datediff(wk,@b,@e),@b)) <= 6
and datepart(dw,@e) > = 6
or datepart(dw,dateadd(wk,datediff(wk,@b,@e),@b)) > datepart(dw,@e)
and dateadd(dw,datediff(wk,@b,@e),@b) <= 6
then 1 else 0 end as 到今天差几个星期六
到今天差几个星期六
-----------
54
(1 row(s) affected)
[解决办法]
create table #T (dtime datetime,[weekday] varchar(100))
declare @startDtime datetime
declare @endDtime datetime
set @startDtime = '2006/06/13 '
set @endDtime = '2006/06/30 '
while (@startDtime <= @endDtime)
begin
if(datepart(dw,@startDtime) = 7)
begin
insert into #T select @startDtime , 'Saturday '
end
select @startDtime =@startDtime+1
end
select convert(varchar(10),dtime,120)as Dtime, [weekday] from #T
select Count(1) as Count_Saturday from #T
drop table #T
---------------------
dtime weekday
2006-06-17Saturday
2006-06-24Saturday
--------------------
Count_Saturday
2
[解决办法]
select dateadd(day,x,col) 日期, '星期六 ' S from
(
select cast( '2006-06-13 ' as datetime) as col
)a cross join
(
SELECT top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x
FROM(SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN(SELECT 0 i UNION ALL SELECT 8) b3
CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4
CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5
CROSS JOIN(SELECT 0 i UNION ALL SELECT 64) b6
CROSS JOIN(SELECT 0 i UNION ALL SELECT 128) b7
CROSS JOIN(SELECT 0 i UNION ALL SELECT 256) b8
order by 1
)b
where datepart(dw,dateadd(day,x,col))=7 AND dateadd(day,x,col) <= '2006-06-30 '
/*
2006-06-17 00:00:00.000星期六
2006-06-24 00:00:00.000星期六
*/
[解决办法]
不好意思，上面算星期日去了。
纠正：
select dateadd(day,x,col) 日期, '星期六 ' S from
(
select cast( '2006-06-13 ' as datetime) as col
)a cross join
(
SELECT top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x
FROM(SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN(SELECT 0 i UNION ALL SELECT 8) b3
CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4
CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5
CROSS JOIN(SELECT 0 i UNION ALL SELECT 64) b6
CROSS JOIN(SELECT 0 i UNION ALL SELECT 128) b7
CROSS JOIN(SELECT 0 i UNION ALL SELECT 256) b8
order by 1
)b
where datepart(dw,dateadd(day,x,col))=6 AND dateadd(day,x,col) <= '2006-06-30 '
/*
2006-06-16 00:00:00.000星期六
2006-06-23 00:00:00.000星期六
2006-06-30 00:00:00.000星期六
*/
[解决办法]
declare @a datetime,@b datetime,@c int
set @a= '2007-06-3 '
set @b= '2007-06-10 '
set @c=0
if datepart(weekday,@a) <> 7
begin
set @a=dateadd(day,7-datepart(weekday,@a),@a)
end
select @c=datediff(day,@a,@b)
set @c= @c/7
set @c=@c+1
print @c
[解决办法]
--看大家列出的方法，如果楼主需要列出第个星期六的日期，就必须用循环了，如果只需要获取一个天数，我的存储过程比较适用。
--其中为什么用 7-datepart(weekday,@dt))，是因为我的sql server中@@datefirst为7，星期六为每周的第7天，所以用7.
--优化一下：根据datepart和@@datefirst计算出给定日期的实际星期数
(datepart(weekday,@dt)+@@datefirst-1)%7
--将存储过程修改为：
create proc getSaturdayCount(
@dt datetime
)as
begin
declare @lastdateOfMonth datetime
--找出本月的最后一天
set @lastdateOfMonth=dateadd(day,-1,convert(char(8),dateadd(month,1,@dt),120)+ '1 ')
--本月的最后一天最后一天减去最近的一个星期六，得出最近的周六到月末的天数，再除以7，得有几个星期六
return (datediff(dd,@dt,@lastdateOfMonth)-(6-(datepart(weekday,@dt)+@@datefirst-1)%7))/7+1
end
