try this
--By Rajat Bhalla
ALTER FUNCTION [dbo].[fnGetDateDiffAsYMD] (@FromDate AS DateTime,@ToDate AS DATETIME)
--Year,Date and Month section modified By ---------------Rajat Bhalla--------------------
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @date datetime,
@tmpdate datetime,
@years int,
@months int,
@days int,
@exp varchar(30),
@mm int,
@experiance datetime
if (datediff(dd,@FromDate ,@ToDate)< 0) or (@FromDate='') or (@ToDate is null)
select @exp ='Invalid joining date'
else
begin
select @experiance=Dateadd(yy,Datediff(yy,@FromDate,@ToDate),@fromDate)
select @years=Datediff(yy,@FromDate,@ToDate) - (CASE
WHEN @experiance > @ToDate THEN 1
ELSE 0
END)
select @months=Month(@ToDate - @experiance) -1
select @days = Day(@ToDate - @experiance) - 1
if @years<=0 and @months<=0 and @days<=0
set @exp = '0';
else if @years<=0
begin
if @months>0
begin
if @days>0
begin
if @months>1
begin
if @days>1
set @exp= CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Day'
end
else if @months=1
begin
if @days>1
set @exp= CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Day'
end
end
else if @days<=0
begin
if @months>1
set @exp= CAST(@months as varchar) + ' months'
else if @months=1
set @exp= CAST(@months as varchar) + ' month'
end
end
else if @months<=0
if @days>1
set @exp = CAST(@days as varchar) + ' Days'
else if @days=1
set @exp = CAST(@days as varchar) + ' Day'
end
else if @years>0 and @months>0 and @days>0
begin
if @years>1
begin
if @months>1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Day'
end
else if @months=1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Day'
end
end
else if @years=1
begin
if @months>1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' months ' +'and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' months ' +'and ' + CAST(@days as varchar) + ' Day'
end
else if @months=1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' month ' +'and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' month ' +'and ' + CAST(@days as varchar) + ' Day'
end
end
end
else if @years>0 and @days>0 and @months<=0
begin
if(@years>1)
begin
if(@days>1)
set @exp = CAST(@years as varchar) +' Years' +' and ' + CAST(@days as varchar) + ' Days'
else if(@days=1)
set @exp = CAST(@years as varchar) +' Years' +' and ' + CAST(@days as varchar) + ' Day'
end
else if(@years=1)
begin
if(@days>1)
set @exp = CAST(@years as varchar) +' Year' +' and ' + CAST(@days as varchar) + ' Days'
else if(@days=1)
set @exp = CAST(@years as varchar) +' Year' +' and ' + CAST(@days as varchar) + ' Day'
end
end
else if @years>0 and @days<=0 and @months<=0
begin
if @years>1
set @exp = CAST(@years as varchar) +' Years'
else if @years=1
set @exp = CAST(@years as varchar) +' Year'
end
else if @years>0 and @days<=0 and @months>0
begin
if @years>1
begin
if @months>1
set @exp = CAST(@years as varchar) +' Years' +' and ' + cast(@months as varchar) + ' months'
else if @months=1
set @exp = CAST(@years as varchar) +' Years' +' and ' + cast(@months as varchar) + ' month'
end
else if @years=1
begin
if @months>1
set @exp = CAST(@years as varchar) +' Year' +' and ' + cast(@months as varchar) + ' months'
else if @months=1
set @exp = CAST(@years as varchar) +' Year' +' and ' + cast(@months as varchar) + ' month'
end
end
end
return @exp
END