Post
Ask Question

How To Calculate Time (Years, Months, And Days) Between Two Dates

In this post, we will learn how to calculate the time between two dates in year, month, and day format. There is a special function for getting a perfect year, month, and day count.

Normally, we use the DATEDIFF function of SQL for getting the years, months, and days between two dates but this function returns a perfect difference between the dates. Let's start creating this function.

Write the below code to create a function in the existing SQL database.

  1. CREATE FUNCTION Get_YearMonthDayCount_Custom  
  2. (  
  3.     @FromDate DateTime  
  4.     ,@ToDate DateTime  
  5. )  
  6. RETURNS Varchar(100)  
  7. AS  
  8. BEGIN  
  9.     DECLARE @Year Int = 0, @Month Int = 0, @Day Int = 0, @Date DateTime, @ReturnValue Int = 0  
  10.   
  11.     SET @Date = @FromDate  
  12.   
  13.     --CALCULATE THE @YEAR   
  14.     IF(DATEPART(YEAR,@ToDate)>DATEPART(YEAR,@FromDate))  
  15.     BEGIN  
  16.         SET @Year = DATEDIFF(YEAR,@FromDate,@ToDate)  
  17.         SET @Date = DATEADD(YEAR,@Year,@FromDate) --UPDATE RUNNINGDATE  
  18.         IF(@Date>@ToDate)  
  19.         BEGIN  
  20.             SET @Year = DATEDIFF(YEAR,@FromDate,DATEADD(YEAR,-1,@ToDate)) --CALCULATE YEARS FROM @FROMDATE TO @TODATE - 1 YEAR  
  21.             SET @Date = DATEADD(YEAR,@Year,@FromDate) --UPDATE RUNNINGDATE  
  22.         END  
  23.     END  
  24.   
  25.     --ADD 1 MONTH AS LONG AS RUNNING DATE IS SMALLER THAN OR EQUAL TO @TODATE  
  26.     WHILE @Date<=@ToDate  
  27.     BEGIN  
  28.         SET @Date = DATEADD(MONTH,1,@Date)  
  29.         IF (@Date<=@ToDate)  
  30.         BEGIN   
  31.             SET @Month=@Month+1  
  32.         END  
  33.     END  
  34.    
  35.     --SET @DATE BACK 1 MONTH  
  36.     SET @Date=DATEADD(MONTH,-1,@Date)  
  37.       
  38.     --START TO COUNT DAYS  
  39.     WHILE @Date<@ToDate   
  40.     BEGIN  
  41.         SET @Date=DATEADD(DAY,1,@Date)  
  42.         SET @Day=@Day+1  
  43.     END  
  44.       
  45.     RETURN CONCAT(CONVERT(NVARCHAR(10),@Year),' Year ',CONVERT(NVARCHAR(10),@Month),' Month ',CONVERT(NVARCHAR(10),@Day),' Day')  
  46. END 
How to call the above function.
  1. DECLARE @FromDate DateTime, @ToDate DateTime  
  2.   
  3. SET @FromDate = '2018-09-01 17:52:01.467'  
  4. SET @ToDate = '2018-10-19 17:52:01.467'  
  5.   
  6. SELECT dbo.Get_YearMonthDayCount_Custom(@FromDate,@ToDate) 
Output
 
Output