Extract amount of years, month and days from a date-range in SQL Server

Question

I am looking for a possibility to extract years, months and days from a date range.

Example:

From: 01/01/2012 To: 08/17/2014
Result: 2 years, 8 month and 17 days

I know how to code this but maybe someone has a genius solution how to do this in SQL with built in commands. Or already made a fast and good working function.

If not I will need to code a sql inline function and present this for documentation later here as answer.

Well, I want one functions that deliver me the result as string to put it into a report. But a functions with ´int´ results and build the string later wil do, too. — YvesR, Apr 28 '14 at 12:04
Leap years and wanting months in the results can lead to some oddities. For instance, on 29th February 2012, if 28th Feburary 2011 was 1 year, 0 months and 1 day ago, what value do you get when you ask again on 1st March 2012? — Damien_The_Unbeliever, Apr 28 '14 at 12:04
@Damien_The_Unbeliever Exactly your point, that is the reason why I can't just get DATEDIFF from days and start use MOD/365. So what I try to find is a function where I get exact results including consider switching years. — YvesR, Apr 28 '14 at 12:11
But the point I'm making is that no-one has actually ever come up with a satisfactory answer to this without having to compromise on one or more edge cases. Given that, you need to think about these edge cases and decide, for yourself, what answers make sense. There's no "correct" answer. — Damien_The_Unbeliever, Apr 28 '14 at 12:14
I tried to find similar answers on SO before asked myself, do you have any link to questions similar like my one so I can compare? — YvesR, Apr 28 '14 at 12:16
possible duplicate of [MS SQL Server : calculate age with accuracy of hours and minuets](http://stackoverflow.com/questions/23242773/ms-sql-server-calculate-age-with-accuracy-of-hours-and-minuets) — Kevin Hogg, Apr 29 '14 at 09:25

score 0 · Answer 1 · answered Apr 29 '14 at 08:43

So first of all I was thinking about the approach to change the orbit around the sun to correct it to exact 360 days a year. But this would probably ends in some bad side effects...

As I want the exact amount I write this (see below) and it seems to work as intended for my needs. I share this for discussions, improvements and to share if anyone need this, too.

DECLARE @start datetime;
DECLARE @end datetime;
DECLARE @current datetime;
DECLARE @year int
DECLARE @month int
DECLARE @days int;
DECLARE @daytmp int;

-- note start date earliest: 1/1/1753
SET @start = CAST('28.02.1753' AS datetime)
SET @end = GETDATE() --CAST('31.12.2016' AS datetime)
SET @current = @start

SET @days = (SELECT DATEDIFF(day, @start, @end))
SET @year = 0;

WHILE @days>365
BEGIN
  SET @days = @days - (SELECT DATEDIFF(day, @current, DATEADD(YEAR, 1, @current)))
  SET @current = DATEADD(YEAR, 1, @current)
  SET @year = @year + 1
END

SET @month = 0;
SET @daytmp = @days

WHILE @daytmp>28
BEGIN
  SET @daytmp = @days - (SELECT DATEDIFF(day, @current, DATEADD(MONTH, 1, @current)))
  IF (@daytmp>0) BEGIN
    SET @days = @daytmp
    SET @current = DATEADD(MONTH, 1, @current)
    SET @month = @month + 1
  END
END

PRINT @year
PRINT @month
PRINT @days

I moved this in to table function that returns 3 values with position 1,2,3 so I can use it inside select statements.

CREATE FUNCTION dbo.sf_GetYearMonthDayFromRange(@start datetime, @end datetime)
RETURNS @result TABLE ([value] int, [position] int)
AS
BEGIN
  DECLARE @current datetime;
  DECLARE @year int
  DECLARE @month int
  DECLARE @days int;
  DECLARE @daytmp int;

  SET @current = @start
  SET @days = (SELECT DATEDIFF(day, @start, @end))
  SET @year = 0;

  WHILE @days>365
  BEGIN
    SET @days = @days - (SELECT DATEDIFF(day, @current, DATEADD(YEAR, 1, @current)))
    SET @current = DATEADD(YEAR, 1, @current)
    SET @year = @year + 1
  END

  SET @month = 0;
  SET @daytmp = @days

  WHILE @daytmp>28
  BEGIN
    SET @daytmp = @days - (SELECT DATEDIFF(day, @current, DATEADD(MONTH, 1, @current)))
    IF (@daytmp>0) BEGIN
      SET @days = @daytmp
      SET @current = DATEADD(MONTH, 1, @current)
      SET @month = @month + 1
    END
  END

  INSERT INTO @result SELECT @year,  1
  INSERT INTO @result SELECT @month, 2
  INSERT INTO @result SELECT @days,  3

  RETURN
END

So, per my comment example, it's 1 year, 1 month and 3 days from 2011-02-28 until 2012-03-01? That's a surprise to me, but that's why I indicated in the comments that there's no agreement on the right answers (and why describing this as "exact" is a bit of a misnomer). By the way, for your first code sample, it's usually better to specify strings that you want to convert to dates without specifying the format, using `YYYYMMDD` with no separators - SQL Server never gets that wrong no matter what the language settings. `DD.MM.YYYY` might be interpreted as `MM.DD.YYYY`. — Damien_The_Unbeliever, Apr 29 '14 at 08:52
Your example should give 1 year, 0 month and 2 days, this is my result and what I expected. — YvesR, Apr 29 '14 at 08:58

score 0 · Answer 2 · answered Apr 29 '14 at 09:01

0

how about this one query

Declare @FDate DateTime = '01/01/2012',
@TDate DateTime = '08/17/2014'

Select Convert(Varchar,(TotDays/365)) + 'years, ' + Convert(Varchar,(TotDays%365)/30) + '     month and ' + Convert(Varchar,(TotDays%365%30)) + ' days'
From (
    Select DATEDIFF(DAY,@FDate, @TDate) TotDays
    ) As DCal

and output above this query is :

Descr
---------------------------
2years, 7 month and 19 days

answered Apr 29 '14 at 09:01

Ritesh Khatri

484
4
13

This is a "simple" solution if you ignore leaping years which have 366 days. So yes, I had this approach, too, but wanted it a bit more exact. – YvesR Apr 29 '14 at 09:05

Extract amount of years, month and days from a date-range in SQL Server

2 Answers2