create function Date_Add
(
@Date datetime,
@DateStr varchar(100)
)returns datetime
as
begin
declare @bz int,@s varchar(100),@i int
if @Date is null or @DateStr is null
return null
if @DateStr = '' return @Date
select @bz = case when left(@DateStr,1)='-' then -1 else 1 end,
@DateStr = case when left(@DateStr,1)='-' then stuff(@DateStr,1,1,'') else rtrim(ltrim(@DateStr)) end
if charindex(' ',@DateStr)>1 or charindex('.',@DateStr)>1
begin
select @i = charindex(' ',@DateStr),@s = left(@DateStr,@i-1)+'-',@DateStr = stuff(@DateStr,1,@i,''),
@i = 0
while @s>'' and @i<3
select @Date = case @i when 0 then dateadd(year,@bz*left(@s,charindex(@s,'-')-1),@Date)
when 1 then dateadd(month,@bz*left(@s,charindex(@s,'-')-1),@Date)
when 2 then dateadd(day,@bz*left(@s,charindex(@s,'-')-1),@Date) end,
@s = stuff(@s,1,charindex(@s,'-'),''),@i = @i+1
select @i = charindex('.',@DateStr),@s = left(@DateStr,@i-1)+':',@DateStr = stuff(@DateStr,1,@i,'')+'.',@i=0
while @s >''and @i<3
select @Date = case @i when 0 then dateadd(Hour,@bz*left(@s,charindex(@s,':')-1),@Date)
when 1 then dateadd(minute,@bz*left(@s,charindex(@s,':')-1),@Date)
when 2 then dateadd(second,@bz*left(@s,charindex(@s,':')-1),@Date) end,
@s = stuff(@s,1,charindex(@s,':'),''),@i = @i+1
end
select @Date = dateadd(millisecond,@bz*left(@DateStr,charindex(@DateStr,'.')-1),@Date)
return (@Date)
enddeclare @Date datetime
select @Date = Date_Add('2002-7-3 13:12:14.4','3-4-4 1:2:12.5')
print @Date--报得是这个错
服务器: 消息 195,级别 15,状态 10,行 2
'Date_Add' 不是可以识别的 函数名。
(
@Date datetime,
@DateStr varchar(100)
)returns datetime
as
begin
declare @bz int,@s varchar(100),@i int
if @Date is null or @DateStr is null
return null
if @DateStr = '' return @Date
select @bz = case when left(@DateStr,1)='-' then -1 else 1 end,
@DateStr = case when left(@DateStr,1)='-' then stuff(@DateStr,1,1,'') else rtrim(ltrim(@DateStr)) end
if charindex(' ',@DateStr)>1 or charindex('.',@DateStr)>1
begin
select @i = charindex(' ',@DateStr),@s = left(@DateStr,@i-1)+'-',@DateStr = stuff(@DateStr,1,@i,''),
@i = 0
while @s>'' and @i<3
select @Date = case @i when 0 then dateadd(year,@bz*left(@s,charindex(@s,'-')-1),@Date)
when 1 then dateadd(month,@bz*left(@s,charindex(@s,'-')-1),@Date)
when 2 then dateadd(day,@bz*left(@s,charindex(@s,'-')-1),@Date) end,
@s = stuff(@s,1,charindex(@s,'-'),''),@i = @i+1
select @i = charindex('.',@DateStr),@s = left(@DateStr,@i-1)+':',@DateStr = stuff(@DateStr,1,@i,'')+'.',@i=0
while @s >''and @i<3
select @Date = case @i when 0 then dateadd(Hour,@bz*left(@s,charindex(@s,':')-1),@Date)
when 1 then dateadd(minute,@bz*left(@s,charindex(@s,':')-1),@Date)
when 2 then dateadd(second,@bz*left(@s,charindex(@s,':')-1),@Date) end,
@s = stuff(@s,1,charindex(@s,':'),''),@i = @i+1
end
select @Date = dateadd(millisecond,@bz*left(@DateStr,charindex(@DateStr,'.')-1),@Date)
return (@Date)
enddeclare @Date datetime
select @Date = Date_Add('2002-7-3 13:12:14.4','3-4-4 1:2:12.5')
print @Date--报得是这个错
服务器: 消息 195,级别 15,状态 10,行 2
'Date_Add' 不是可以识别的 函数名。
select @Date = dbo.Date_Add( '2002-7-3 13:12:14.4 ', '3-4-4 1:2:12.5 ')
print @Date
(
@Date datetime,
@DateStr varchar(100)
)returns datetime
as
begin
declare @bz int,@s varchar(100),@i int
if @Date is null or @DateStr is null
return null
if @DateStr = ' ' return @Date
select @bz = case when left(@DateStr,1)= '- ' then -1 else 1 end,
@DateStr = case when left(@DateStr,1)= '- ' then stuff(@DateStr,1,1, ' ') else rtrim(ltrim(@DateStr)) end
if charindex( ' ',@DateStr) >1 or charindex( '. ',@DateStr) >1
begin
select @i = charindex( ' ',@DateStr),@s = left(@DateStr,@i-1)+ '- ',@DateStr = stuff(@DateStr,1,@i, ' '),
@i = 0
while @s > ' ' and @i <3
select @Date = case @i when 0 then dateadd(year,@bz*left(@s,charindex(@s, '- ')-1),@Date)
when 1 then dateadd(month,@bz*left(@s,charindex(@s, '- ')-1),@Date)
when 2 then dateadd(day,@bz*left(@s,charindex(@s, '- ')-1),@Date) end,
@s = stuff(@s,1,charindex(@s, '- '), ' '),@i = @i+1 select @i = charindex( '. ',@DateStr),@s = left(@DateStr,@i-1)+ ': ',@DateStr = stuff(@DateStr,1,@i, ' ')+ '. ',@i=0
while @s > ' 'and @i <3
select @Date = case @i when 0 then dateadd(Hour,@bz*left(@s,charindex(@s, ': ')-1),@Date)
when 1 then dateadd(minute,@bz*left(@s,charindex(@s, ': ')-1),@Date)
when 2 then dateadd(second,@bz*left(@s,charindex(@s, ': ')-1),@Date) end,
@s = stuff(@s,1,charindex(@s, ': '), ' '),@i = @i+1
end
select @Date = dateadd(millisecond,@bz*left(@DateStr,charindex(@DateStr, '. ')-1),@Date)
return (@Date)
end
GO
declare @Date datetime
select @Date = DBO.Date_Add( '2002-07-03 13:12:14.4 ', '3-4-4 1:2:12.5 ')
print @Date
drop function Date_Add要加GO,引用要加dbo
select @Date = dbo.Date_Add( '2002-7-3 13:12:14.4 ', '3-4-4 1:2:12.5 ')
print @Date
--改为:
select @Date = dbo.Date_Add( '2002-7-3 13:12:14.4 ', '3-4-4 1:2:12.5 ')
服务器: 消息 536,级别 16,状态 3,过程 Date_Add,行 31
向 substring 函数传递了无效的 length 参数。
(
@Date datetime,
@DateStr varchar(100)
)returns datetime
as
begin
declare @bz int,@s varchar(100),@i int
if @Date is null or @DateStr is null
return null
if @DateStr = ' ' return @Date
select @bz = case when left(@DateStr,1)= '- ' then -1 else 1 end,
@DateStr = case when left(@DateStr,1)= '- ' then stuff(@DateStr,1,1, ' ') else rtrim(ltrim(@DateStr)) end
if charindex( ' ',@DateStr) >1 or charindex( '. ',@DateStr) >1
begin
select @i = charindex( ' ',@DateStr),@s = left(@DateStr,@i-1)+ '- ',@DateStr = stuff(@DateStr,1,@i, ' '),
@i = 0
while @s > ' ' and @i <3
select @Date = case @i when 0 then dateadd(year,@bz*left(@s,charindex(@s, '- ')-1),@Date)
when 1 then dateadd(month,@bz*left(@s,charindex(@s, '- ')-1),@Date)
when 2 then dateadd(day,@bz*left(@s,charindex(@s, '- ')-1),@Date) end,
@s = stuff(@s,1,charindex(@s, '- '), ' '),@i = @i+1 select @i = charindex( '. ',@DateStr),@s = left(@DateStr,@i-1)+ ': ',@DateStr = stuff(@DateStr,1,@i, ' ')+ '. ',@i=0
while @s > ' 'and @i <3
select @Date = case @i when 0 then dateadd(Hour,@bz*left(@s,charindex(@s, ': ')-1),@Date)
when 1 then dateadd(minute,@bz*left(@s,charindex(@s, ': ')-1),@Date)
when 2 then dateadd(second,@bz*left(@s,charindex(@s, ': ')-1),@Date) end,
@s = stuff(@s,1,charindex(@s, ': '), ' '),@i = @i+1
end
select @Date = dateadd(millisecond,@bz*STUFF(@DateStr,1,charindex('.', @DateStr),''),@Date)
return (@Date)
end
GO
declare @Date datetime
select @Date = DBO.Date_Add( '2002-07-03 13:12:14.4 ', '3-4-4 1:2:12.5 ')
print @Date
drop function Date_Add改好了
select @Date = dateadd(millisecond,@bz*left(@DateStr,charindex(@DateStr, '. ')-1),@Date)
这里charindex(@DateStr, '. ')方法用错了,
第二,给楼主一个建议就是,写的时候给点注释,第三,花点精力来对程序排排版面!
把这个函数也贴出来看看。
服务器: 消息 536,级别 16,状态 3,过程 Date_Add,行 31
向 substring 函数传递了无效的 length 参数。
呵呵。应该反过来吧。
charindex('.',@DateStr)--这样才是得到'.'在变量字符串@DateStr中的位置
是这个意思吗!?
在这个时间上 加上3年零4个月零4天 零1小时2分12.5秒
(
@Date datetime,
@DateStr varchar(100)
)returns datetime
as
begin
declare @bz int,@s varchar(100),@i int
if @Date is null or @DateStr is null
return null
if @DateStr = ' ' return @Date
select @bz = case when left(@DateStr,1)= '- ' then -1 else 1 end,
@DateStr = case when left(@DateStr,1)= '- ' then stuff(@DateStr,1,1, ' ') else rtrim(ltrim(@DateStr)) end
if charindex( ' ',@DateStr) >1 or charindex( '. ',@DateStr) >1
begin
select @i = charindex( ' ',@DateStr),@s = left(@DateStr,@i-1)+ '- ',@DateStr = stuff(@DateStr,1,@i, ' '),
@i = 0
while @s > ' ' and @i <3
select @Date = case @i when 0 then dateadd(year,@bz*left(@s,charindex(@s, '- ')-1),@Date)
when 1 then dateadd(month,@bz*left(@s,charindex(@s, '- ')-1),@Date)
when 2 then dateadd(day,@bz*left(@s,charindex(@s, '- ')-1),@Date) end,
@s = stuff(@s,1,charindex(@s, '- '), ' '),@i = @i+1 select @i = charindex( '. ',@DateStr),@s = left(@DateStr,@i-1)+ ': ',@DateStr = stuff(@DateStr,1,@i, ' ')+ '. ',@i=0
while @s > ' 'and @i <3
select @Date = case @i when 0 then dateadd(Hour,@bz*left(@s,charindex(@s, ': ')-1),@Date)
when 1 then dateadd(minute,@bz*left(@s,charindex(@s, ': ')-1),@Date)
when 2 then dateadd(second,@bz*left(@s,charindex(@s, ': ')-1),@Date) end,
@s = stuff(@s,1,charindex(@s, ': '), ' '),@i = @i+1
end
select @Date = dateadd(millisecond,@bz*STUFF(@DateStr,1,charindex('.',@DateStr),''),@Date)
return (@Date)
end
GO
declare @Date datetime
select @Date = DBO.Date_Add('2002-07-03 13:12:14.4','3-4-4 1:2:12.5')
print @Date
drop function Date_Add
为什么时间没变呀
函数我改了下:
alter function Date_Add(@Date datetime,@DateStr varchar(100)
)returns datetime
as
begin
declare @y int
declare @m int
declare @d int
declare @pp varchar(80)
declare @h int
declare @mm int
declare @s numeric(5,2)
declare @i int
if @Date is null or @DateStr is null return null
if isnull(@DateStr,'') = '' return @Date
set @i=charindex('-',@DateStr)
set @y=substring(@DateStr,1,@i-1)
set @DateStr=substring(@DateStr,@i+1,len(@DateStr))
set @i=charindex('-',@DateStr)
set @m=substring(@DateStr,1,@i-1)
set @DateStr=substring(@DateStr,@i+1,len(@DateStr))
set @i=charindex(' ',@DateStr)
set @d=substring(@DateStr,1,@i-1)set @DateStr=substring(@DateStr,charindex(' ',@DateStr)+1,len(@DateStr))
set @i=charindex(':',@DateStr)
set @h=substring(@DateStr,1,@i-1)
set @DateStr=substring(@DateStr,@i+1,len(@DateStr))
set @i=charindex(':',@DateStr)
set @mm=substring(@DateStr,1,@i-1)
set @s=substring(@DateStr,@i+1,len(@DateStr))
set @Date=datename(yy,dateadd(yy,@y,@Date))+'-'+datename(mm,dateadd(mm,@m,@Date))+'-'+datename(dd,dateadd(dd,@d,@Date))+' '+
datename(hh,dateadd(hh,@h,@Date))+':'+datename(mi,dateadd(mi,@mm,@Date))+':'+datename(ss,dateadd(ss,@s,@Date))
return @Date
end
GO 调用:select DBO.Date_Add( '2002-07-03 3:12:14.4 ', '5-4-44 5:20:12.5')