CREATE FUNCTION f_DateADD(
@Date datetime,
@DateStr varchar(23)
) RETURNS datetime
想运行此函数,这样写为什么报错?应该如何使用?
f_DateADD('2001-1-01','2001-2-1')
@Date datetime,
@DateStr varchar(23)
) RETURNS datetime
想运行此函数,这样写为什么报错?应该如何使用?
f_DateADD('2001-1-01','2001-2-1')
select dbo.f_DateADD('2001-1-01','2001-2-1')
create table tab1(CompanyPropertyID varchar(10),CompanyPropertyName varchar(100))
Insert into tab1
select '01','aaa'
union all select '02','bbb'
union all select '03','ccc'
create table tab2(SellID varchar(10),CompanyPropertyIDS varchar(100))
Insert into tab2
select 'A001','01;02;'
union all select 'A001','01'
union all select 'A001','01;03;'
union all select 'A001','01;02;03;'
select * from tab1
select * from tab2
--創建函數處理
create function dbo.fn_f(@s varchar(1000))
returns varchar(1000)
as
begin
declare @a varchar(1000)
declare @i int
set @a=''
set @i=1
while @i>0
begin
select @a=@a+CompanyPropertyName+';' from tab1 where CompanyPropertyID=substring(@s,@i,2)
set @i=charindex(';',@s,@i+3)-2
end
return(left(@a,len(@a)-1))
end
--刪除
drop table tab1
drop table tab2
drop function dbo.fn_f
--結果
select SellID,CompanyPropertyName=dbo.fn_f(CompanyPropertyIDS) from tab2
SellID CompanyPropertyName
---------------------------------------------
A001 aaa;bbb
A001 aaa
A001 aaa;ccc
A001 aaa;bbb;ccc
dbo.f_DateADD
@Date datetime,
@DateStr varchar(23)
) RETURNS datetime
AS
BEGIN
DECLARE @bz int, @s varchar(12), @i int
IF @DateStr IS NULL OR @Date IS NULL
OR(CHARINDEX('.',@DateStr))>0
AND @DateStr NOT LIKE '%[:]%[:]%.%'
RETURN(NULL)
IF @DateStr = '' RETURN(@Date)
SELECT @bz = CASE
WHEN LEFT(@DateStr,1) = '-' THEN -1
ELSE 1 END,
@DateStr = CASE
WHEN LEFT(@Date,1) = '-'
THEN STUFF(RTRIM(LTRIM(@DateStr)),1,1,'')
ELSE RTRIM(LTRIM(@DateStr)) END
IF CHARINDEX('',@DateStr)>1
OR CHARINDEX('-',@DateStr)>1
OR (CHARINDEX('.',@DateStr) = 0
AND CHARINDEX(':',@DateStr)=0)
BEGIN
SELECT @i = CHARINDEX(' ',@DateStr + ' ')
,@s = REVERSE(LEFT(@DateStr,@i - 1)) + '-'
,@DateStr = STUFF(@DateStr,1,@i,'')
,@i = 0
WHILE @s >'' AND @i <3
SELECT @Date = CASE @i
WHEN 0 THEN
DATEADD(Day,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@S)-1)),@Date)
WHEN 1 THEN
DATEADD(Month,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@S)-1)),@Date)
WHEN 2 THEN
DATEADD(Year,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@S)-1)),@Date)
END,
@s = STUFF(@s,1,CHARINDEX('-',@S),''),
@i = @i + 1
END
IF @DateStr > ''
BEGIN
IF CHARINDEX('.',@DateStr)>0
SELECT @Date = DATEADD(Millisecond
,@bz*STUFF(@DateStr,1,CHARINDEX('.',@DateStr),''),
@Date),
@DateStr = LEFT(@DateStr,CHARINDEX('.',@DateStr)-1) + ':',
@i = 0
ELSE
SELECT @DateStr = @Date + ':',@i=0
WHILE @DateStr >'' and @i < 3
SELECT @Date = CASE @i
WHEN 0 THEN
DATEADD(Hour,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)
WHEN 1 THEN
DATEADD(Minute,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)
WHEN 2 THEN
DATEADD(Second,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)
END,
@DateStr = STUFF(@DateStr,1,CHARINDEX(':',@DateStr),''),
@i = @i + 1
END
RETURN(@Date)
END
我把函数粘贴出来,看看怎么调用
用select dbo.f_DateADD('2001-1-01','2001-2-1')
报错如下
服务器: 消息 207,级别 16,状态 3,行 1
列名 'f_DateADD' 无效。
4002-03-02 00:00:00.000
完美的例子
CREATE FUNCTION f_DateADD(@Date datetime, @DateStr varchar(23) )
RETURNS datetime
begin
declare @@Date datetime
select @@Date=datediff(year,@Date,convert(datetime,@DateStr))
return @@Date
end
go
select dbo.f_DateADD('2001-1-01','2001-2-1')
/*
-----------------------
1900-01-01 00:00:00.000
*/
CREATE FUNCTION dbo.f_DateADD(
@Date datetime,
@DateStr varchar(23)
) RETURNS datetime
as
begin
--语句
end
dbo.f_DateADD('2001-1-01','2001-2-1')