How can I concat the int with a string

Question

I get the 1 but not the text year what is wrong the data type?

FUNCTION [dbo].fndtDiff
RETURNS nvarchar
AS
BEGIN
Declare @vKey int
Declare @Dval nvarchar
Declare @d1 as DateTime
Declare @Result as nvarchar

	If @vStartTime is Not Null   
		If @vEndTime IS NULL   
			SELECT @d1 = DATEDIFF(year, @vEndTime, GETDATE()) from dbo.M_CFR_List  
		ELSE  
			SELECT @d1 = DATEDIFF(year, @vStartTime, @vEndTime) from dbo.M_CFR_List  
	--  
	  

	set @vKey = convert(int,@d1)  
	If @vKey >=1   
		set @dVal =  convert(nvarchar,@vKey )  
		set @Result = Concat(@dVal , '  year' )  
	  
	  
	RETURN @Result  

END  

GO

---- second section

Declare @vStartTime DateTIME
Declare @vEndTime DateTIME

set @vStartTime = '2017/08/25 07:00'
set @vEndTime = '2018/08/25 12:45'

SELECT dbo.fndtDiff(@vStartTime,@vEndTime) AS Result

Answer 1

Hi @Kenneth Folk ,

Please try the following T-SQL as a starting point for your function.

SQL

Declare @vStartTime DATETIME = '2017-08-25 07:00';  
Declare @vEndTime DATETIME = '2018-08-25 12:45';  
  
Declare @d1 INT  
 , @Result VARCHAR(10);  
      
SELECT @d1 = DATEDIFF(year, COALESCE(@vStartTime,GETDATE()), COALESCE(@vEndTime, GETDATE()));  
SET @Result = CONCAT(@d1, ' year(s)' );  
-- Or SQL Server 2016 onwards  
SET @Result = FORMATMESSAGE('%i year(s)', @d1);  
          
SELECT @Result;  

Answer 2

You must always specify a size when defining "[n]varchar" fields. If you do not, you will get strange results.

DATEDIFF returns an INT, not a datetime. @d1 needs to be an int.

https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver16#return-type