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
  11. SET @Date = @FromDate
  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
  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
  35. --SET @DATE BACK 1 MONTH
  36. SET @Date=DATEADD(MONTH,-1,@Date)
  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
  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
  3. SET @FromDate = '2018-09-01 17:52:01.467'
  4. SET @ToDate = '2018-10-19 17:52:01.467'
  6. SELECT dbo.Get_YearMonthDayCount_Custom(@FromDate,@ToDate)
Output
Output