我自己写了一个简单的函数,想实现输入一个日期,能查询出一个任务开始了多少天了,表JobNeedRes里的start_day 是任务的开始时间
表JobNeedRes中字段位 job_code,job_name,start_dayif object_id('tempdb.dbo.JobTemp') is not null drop table JobTemp
create table JobTemp (job_code varchar(10),job_name varchar(10),strat_day varchar(20))
insert into JobTemp
select 'LDPT01','陆地平台01','2008-05-05' union all
select 'LDPT02','陆地平台02','2008-04-20' union all
select 'LDPT03','陆地平台03','2008-05-01'CREATE FUNCTION TestNum(
@date_begin varchar(20) --计算的开始时间
)RETURNS float
AS
BEGIN
DECLARE @neednum float
SELECT @neednum = datediff(d,@date_begin,start_day) from JobTempRETURN(@neednum)
ENDselect job_code,job_name,dbo.TestNum('2008-05-10') from JobTemp
但是我写的函数好像只能求出表JobNeedRes最后一行的记录
——————————
LDPT01 陆地平台01 -9.0
LDPT02 陆地平台02 -9.0
LDPT03 陆地平台03 -9.0 好像是因为SELECT @neednum = datediff(d,@date_begin,start_day) from JobTemp 只选中了最后一行,但是不怎么修改,大家帮帮看看吧,谢了!
表JobNeedRes中字段位 job_code,job_name,start_dayif object_id('tempdb.dbo.JobTemp') is not null drop table JobTemp
create table JobTemp (job_code varchar(10),job_name varchar(10),strat_day varchar(20))
insert into JobTemp
select 'LDPT01','陆地平台01','2008-05-05' union all
select 'LDPT02','陆地平台02','2008-04-20' union all
select 'LDPT03','陆地平台03','2008-05-01'CREATE FUNCTION TestNum(
@date_begin varchar(20) --计算的开始时间
)RETURNS float
AS
BEGIN
DECLARE @neednum float
SELECT @neednum = datediff(d,@date_begin,start_day) from JobTempRETURN(@neednum)
ENDselect job_code,job_name,dbo.TestNum('2008-05-10') from JobTemp
但是我写的函数好像只能求出表JobNeedRes最后一行的记录
——————————
LDPT01 陆地平台01 -9.0
LDPT02 陆地平台02 -9.0
LDPT03 陆地平台03 -9.0 好像是因为SELECT @neednum = datediff(d,@date_begin,start_day) from JobTemp 只选中了最后一行,但是不怎么修改,大家帮帮看看吧,谢了!
@date_begin varchar(20), --计算的开始时间
@job_code varchar(20)
)RETURNS float
AS
BEGIN
DECLARE @neednum float
SELECT @neednum = datediff(d,@date_begin,start_day) from JobTemp where job_code=@job_codeRETURN(@neednum)
END
GOselect job_code,job_name,dbo.TestNum('2008-05-10',job_code) from JobTemp
@date_begin varchar(20), --计算的开始时间
@job_code varchar(20)
)RETURNS float
AS
BEGIN
DECLARE @neednum float
SELECT @neednum = datediff(d,@date_begin,start_day) from JobTemp where job_code=@job_codeRETURN(@neednum)
END
GOselect job_code,job_name,dbo.TestNum('2008-05-10',job_code) from JobTemp
create table JobTemp (job_code varchar(10),job_name varchar(10),strat_day varchar(20))
insert into JobTemp
select 'LDPT01','陆地平台01','2008-05-05' union all
select 'LDPT02','陆地平台02','2008-04-20' union all
select 'LDPT03','陆地平台03','2008-05-01'--CREATE FUNCTION TestNum(
--@date_begin varchar(20) --计算的开始时间
--)RETURNS float
--AS
--BEGIN
--DECLARE @neednum float
--SELECT @neednum = datediff(d,@date_begin,strat_day) from JobTemp
--
--RETURN(@neednum)
--ENDselect job_code,job_name,datediff(day,'2008-05-10',strat_day) from JobTemp
@date_begin varchar(20) --计算的开始时间
, @job_Code varchar(10)
)RETURNS float
AS
BEGIN
DECLARE @neednum float
SELECT @neednum = datediff(d,@date_begin,strat_day) from JobTemp where job_Code =@job_Code RETURN(@neednum)
END加个参数
多谢 Limpire 帮我解决的好多问题了