my query is to creating a function calculate the age of an input birth date.
It can execute successfully, but when I use the function by type "select dbo.PersonsAge('19900701')". it shows error "Conversion failed when converting the varchar value 'years' to data type int." Please HELP ! I even don't know where do I need to convert the data type from varchar to int. Thank you! create function dbo.PeopleAge(@BirthDate DateTime)
returns Char(30)
as
begin declare @Agemonth Char(20)
declare @Ageyear Char(20)
declare @Age Char(30)
set @Ageyear = convert(Char(20),datediff(year, @BirthDate, getdate()))
set @Agemonth = convert(Char(20),datediff(month,@BirthDate, getdate())) if (@Ageyear=0)
if ((@Agemonth%12)=0)
set @Age = (select @Ageyear+' year')
if ((@Agemonth%12)=1)
set @Age = (select @Agemonth+' month')
else
set @Age= (select @Agemonth + ' months') if (@Ageyear=1)
if ((@Agemonth%12)=0 )
set @Age = (select @Ageyear+' year')
if ((@Agemonth%12)=1)
set @Age = (select @Ageyear+' year'+ @Agemonth+' month')
else
set @Age= (select @Ageyear+' year'+ @Agemonth + ' months') if (@Ageyear > 1)
if ((@Agemonth%12)=0)
set @Age = (select @Ageyear+' years')
if ((@Agemonth%12)=1)
set @Age = (select @Ageyear+' years'+ @Agemonth+' month')
else
set @Age= (select @Ageyear+' years'+ @Agemonth + ' months') return @Age
end
It can execute successfully, but when I use the function by type "select dbo.PersonsAge('19900701')". it shows error "Conversion failed when converting the varchar value 'years' to data type int." Please HELP ! I even don't know where do I need to convert the data type from varchar to int. Thank you! create function dbo.PeopleAge(@BirthDate DateTime)
returns Char(30)
as
begin declare @Agemonth Char(20)
declare @Ageyear Char(20)
declare @Age Char(30)
set @Ageyear = convert(Char(20),datediff(year, @BirthDate, getdate()))
set @Agemonth = convert(Char(20),datediff(month,@BirthDate, getdate())) if (@Ageyear=0)
if ((@Agemonth%12)=0)
set @Age = (select @Ageyear+' year')
if ((@Agemonth%12)=1)
set @Age = (select @Agemonth+' month')
else
set @Age= (select @Agemonth + ' months') if (@Ageyear=1)
if ((@Agemonth%12)=0 )
set @Age = (select @Ageyear+' year')
if ((@Agemonth%12)=1)
set @Age = (select @Ageyear+' year'+ @Agemonth+' month')
else
set @Age= (select @Ageyear+' year'+ @Agemonth + ' months') if (@Ageyear > 1)
if ((@Agemonth%12)=0)
set @Age = (select @Ageyear+' years')
if ((@Agemonth%12)=1)
set @Age = (select @Ageyear+' years'+ @Agemonth+' month')
else
set @Age= (select @Ageyear+' years'+ @Agemonth + ' months') return @Age
end
if ((@Agemonth%12)=0)
set @Age = (select @Ageyear+' years')
if ((@Agemonth%12)=1)
set @Age = (select @Ageyear+' years'+ @Agemonth+' month')
else
set @Age= (select @Ageyear+' years'+ @Agemonth + ' months') 没看到你这里有INT类型的呀,提示是YEARS这里转换出错,
服务器: 消息 443,级别 16,状态 1,过程 PeopleAge,行 9
在函数内不正确地使用了 'getdate'。
服务器: 消息 443,级别 16,状态 1,过程 PeopleAge,行 10
在函数内不正确地使用了 'getdate'。
as
begin declare @Agemonth Char(20)
declare @Ageyear Char(20)
--declare @Age Char(30)
set @Ageyear = convert(Char(20),datediff(year, @BirthDate, getdate()))
set @Agemonth = convert(Char(20),datediff(month,@BirthDate, getdate())) if (@Ageyear=0)
if ((@Agemonth%12)=0)
set @Age = (select @Ageyear+' year')
if ((@Agemonth%12)=1)
set @Age = (select @Agemonth+' month')
else
set @Age= (select @Agemonth + ' months') if (@Ageyear=1)
if ((@Agemonth%12)=0 )
set @Age = (select @Ageyear+' year')
if ((@Agemonth%12)=1)
set @Age = (select @Ageyear+' year'+ @Agemonth+' month')
else
set @Age= (select @Ageyear+' year'+ @Agemonth + ' months') if (@Ageyear > 1)
if ((@Agemonth%12)=0)
set @Age = (select @Ageyear+' years')
if ((@Agemonth%12)=1)
set @Age = (select @Ageyear+' years'+ @Agemonth+' month')
else
set @Age= (select @Ageyear+' years'+ @Agemonth + ' months')
end 过程没问题