Age Duration Calculator SQL

 Function IN SQL:

        

-        

--SELECT [dbo].[GetYearDuration] (REPLACE(CONVERT(Date,e.DateOfBirth,103),' ','-')  , REPLACE(CONVERT(Date,e.FirstJoiningDate,103),' ','-') ) as JoiningAge          

          

--FROM Employee e          

--WHERE e.EmployeeId = 3734       

  

--SELECT [dbo].[GetDurationYear_Month_Day]('06 June, 1985', '04 July, 2020')   

--SELECT [dbo].[GetDurationYear_Month_Day]('01 April, 1960', '26 March, 1986')   

     

          

ALTER FUNCTION [dbo].[GetDurationYear_Month_Day]           

(          

   @FromDate1 nvarchar(20), @ToDate1 nvarchar(20)          

)          

RETURNS NVARCHAR(300)          

AS          

BEGIN      

   Declare @Message NVARCHAR(300) 

 

if isDate(@FromDate1) = 1 and isDate(@ToDate1) = 1

BEGIN

Declare @FromDate DATETIME, @ToDate DATETIME 

SET @FromDate = @FromDate1

SET @ToDate = @ToDate1

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int

Declare @currentdatetime datetime  

--SELECT @date = '15-Jun-1986'

set @date = @FromDate

  

set @currentdatetime = @ToDate 

 --SELECT

 -- @date = REPLACE(CONVERT(Date,e.DateOfBirth,103),' ','-'),

  

 -- @currentdatetime =  REPLACE(CONVERT(Date,e.FirstJoiningDate,103),' ','-')                                  

 --  FROM Employee as e WHERE e.EmployeeId = 26380 --12390 

   

    

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, @currentdatetime) - CASE WHEN (MONTH(@date) > MONTH(@currentdatetime)) OR (MONTH(@date) = MONTH(@currentdatetime) AND DAY(@date) > DAY(@currentdatetime)) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)

SELECT @months = DATEDIFF(m, @tmpdate, @currentdatetime) - CASE WHEN DAY(@date) > DAY(@currentdatetime) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(m, @months, @tmpdate)

SELECT @days = DATEDIFF(d, @tmpdate, @currentdatetime)

 

SET @Message =  CAST(@years AS VARCHAR(4)) + '-' + CAST(@months AS VARCHAR(4)) + '-' + CAST(@days AS VARCHAR(4)) 

END -- END if

ELSE

BEGIN

SET @Message = 'Problem With Date:GetDurationYear_Month_Day'

END

RETURN @Message;

END