--调用示例 select * from dbo.f_getdate('2003-01-01','2003-01-20') */if exists(select 1 from sysobjects where id=object_id('f_getdate') and objectproperty(id,'IsInlineFunction')=0) drop function f_getdatego create function f_getdate(@dt1 datetime,@dt2 datetime) returns @re table(id int identity(0,1),dt datetime) as begin declare @i int select @i=datediff(day,@dt1,@dt2)+1
while @i>1 begin insert into @re(dt) select top 100 @dt1 from syscolumns set @i=@i-100 end delete from @re where id>datediff(day,@dt1,@dt2)
update @re set dt=dateadd(day,id,dt) return end go
--调用上面的函数实现楼主的要求:insert into WorkDate select dt,datename(weekday,dt),case when datepart(weekday,dt) in(7,1) then 0 else 1 end from dbo.f_getdate('2004-01-01','2004-12-31')
谢谢zjcxc(邹建),我按照你的方法将Sql语句输入SQL查询分析器后运行: create function f_getdate(@dt1 datetime,@dt2 datetime) returns @re table(id int identity(0,1),dt datetime) as begin declare @i int select @i=datediff(day,@dt1,@dt2)+1
while @i>1 begin insert into @re(dt) select top 100 @dt1 from syscolumns set @i=@i-100 end delete from @re where id>datediff(day,@dt1,@dt2)
update @re set dt=dateadd(day,id,dt) return end goinsert into WorkDate select dt,datename(weekday,dt),case when datepart(weekday,dt) in(7,1) then 0 else 1 end from dbo.f_getdate('2004-01-01','2004-12-31')结果出现:服务器: 消息 213,级别 16,状态 4,行 1 插入错误: 列名或所提供值的数目与表定义不匹配。
select top 400 identity(int,0,1) as id into #t from sysobjects A,sysobjects B set datefirst 1insert WorkDate select d, datepart(wd,d), (case when datepart(wd,d)=6 or datepart(wd,d)=7 then 0 else 1 end) from ( select dateadd(day,id,'2004-01-01') as d from #t where year(dateadd(day,id,'2004-01-01'))=2004 ) Tdrop table #t
一个简单的考勤系统
得到两个日期之间的日期列表
--调用示例
select * from dbo.f_getdate('2003-01-01','2003-01-20')
*/if exists(select 1 from sysobjects where id=object_id('f_getdate') and objectproperty(id,'IsInlineFunction')=0)
drop function f_getdatego
create function f_getdate(@dt1 datetime,@dt2 datetime)
returns @re table(id int identity(0,1),dt datetime)
as
begin
declare @i int
select @i=datediff(day,@dt1,@dt2)+1
while @i>1
begin
insert into @re(dt) select top 100 @dt1 from syscolumns
set @i=@i-100
end
delete from @re where id>datediff(day,@dt1,@dt2)
update @re set dt=dateadd(day,id,dt)
return
end
go
select dt,datename(weekday,dt),case when datepart(weekday,dt) in(7,1) then 0 else 1 end
from dbo.f_getdate('2004-01-01','2004-12-31')
create function f_getdate(@dt1 datetime,@dt2 datetime)
returns @re table(id int identity(0,1),dt datetime)
as
begin
declare @i int
select @i=datediff(day,@dt1,@dt2)+1
while @i>1
begin
insert into @re(dt) select top 100 @dt1 from syscolumns
set @i=@i-100
end
delete from @re where id>datediff(day,@dt1,@dt2)
update @re set dt=dateadd(day,id,dt)
return
end
goinsert into WorkDate
select dt,datename(weekday,dt),case when datepart(weekday,dt) in(7,1) then 0 else 1 end
from dbo.f_getdate('2004-01-01','2004-12-31')结果出现:服务器: 消息 213,级别 16,状态 4,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
set datefirst 1insert WorkDate
select d,
datepart(wd,d),
(case when datepart(wd,d)=6 or datepart(wd,d)=7 then 0 else 1 end)
from (
select dateadd(day,id,'2004-01-01') as d
from #t where year(dateadd(day,id,'2004-01-01'))=2004
) Tdrop table #t