declare @dt datetime,@dt1 datetime,@dt2 datetime,@flag bit
declare cur cursor for select dt from tb
open cur
set @flag=0
fetch next from cur into @dt1
set @dt2=@dt1
while @@fetch_status=0
begin
set @dt2=dateadd(day,7,@dt2)
if @flag=0
fetch next from cur into @dt1
if @dt1=@dt2
begin
set @flag=0
fetch next from cur into @dt1
end
else
begin
set @flag=1
print @dt2
end
end
close cur
deallocate cur
declare cur cursor for select dt from tb
open cur
set @flag=0
fetch next from cur into @dt1
set @dt2=@dt1
while @@fetch_status=0
begin
set @dt2=dateadd(day,7,@dt2)
if @flag=0
fetch next from cur into @dt1
if @dt1=@dt2
begin
set @flag=0
fetch next from cur into @dt1
end
else
begin
set @flag=1
print @dt2
end
end
close cur
deallocate cur
returns @t table(d datetime)
as
begin
declare @i int
declare @J int
declare @m int
set @m = 0
set @j = 0
select @i = 60
begin
while @i > 0
begin
while datepart(dw,dateadd(d,@j,@enddate)) <> 6
set @j = @j+1
insert @t select dateadd(d,@j,@enddate)
set @j = @j+1
set @i = @i - 1
end
end
return
end go
select *
from dbo.countday('2005-01-01')
where d not in (select date from yourtable)
drop function countday
要写函数吗?
如果在asp中,能只用sql语句来实现吗??
该如何呢?
要写函数吗?
如果在asp中,能只用sql语句来实现吗??
该如何呢?
------------------- create table t(d datetime)
go
declare @enddate datetime
set @enddate = '2005-01-01'
declare @i int
declare @J int
set @j = 0
select @i = 60
begin
while @i > 0
begin
while datepart(dw,dateadd(d,@j,@enddate)) <> 6
set @j = @j+1
insert t select dateadd(d,@j,@enddate)
set @j = @j+1
set @i = @i - 1
end
end
select *
from t
where d not in (select date from yourtable)
declare cur cursor for select dt from tb
declare @tb table (id int identity,dt datetime)
open cur
set @flag=0
fetch next from cur into @dt1
set @dt2=@dt1
while @@fetch_status=0
begin
set @dt2=dateadd(day,7,@dt2)
if @flag=0
fetch next from cur into @dt1
if @dt1=@dt2
begin
set @flag=0
fetch next from cur into @dt1
end
else
begin
set @flag=1
--print @dt2
insert into @tb values(@dt2)
end
end
close cur
deallocate cur
select dt from @tb
为什么要用60呢?我希望的是用2005-1-1到当前日期为止的周五的数,而不是从2005-1-1往后数60个周五啊to wgsasd311(自强不息)
你的方法我不是很明白啊,运行的时候怎么老是2006-2-20这一条数据啊?
@startdate datetime--起始时间
as
declare @dt1 datetime,@day int
set @day=6-datepart(dw,@startdate)
set @day=case when @day<1 then 7+@day else @day end
select @dt1=dateadd(day,@day,@startdate)
declare @tt table(dt datetime)
while @dt1<=getdate()
begin
if not exists(select 1 from tb where dt=@dt1)
insert into @tt values(@dt1)
set @dt1=dateadd(day,7,@startdate)
end
select * from @tt
goexec p '2005-01-01'
create proc p
@startdate datetime--起始时间
as
declare @dt1 datetime,@day int
set @day=6-datepart(dw,@startdate)
set @day=case when @day<1 then 7+@day else @day end
select @dt1=dateadd(day,@day,@startdate)
declare @tt table(dt datetime)
while @dt1<=getdate()
begin
if not exists(select 1 from tb where dt=@dt1)
insert into @tt values(@dt1)
set @dt1=dateadd(day,7,@dt1)
end
select * from @tt
goexec p '2005-01-01'