Get number of days,month,year from give two dates and type casting

Question

I am trying to get the number of days,month and year from 2dates, but those are getting individual

DECLARE @datecount INT;
DECLARE @monthcount INT;
DECLARE @yearcount INT;

SELECT @datecount = (SELECT DATEDIFF(DAY, usr.registrationdate, GETDATE())
                     FROM   users usr
                     WHERE  usr.userid = @UserId);

SELECT @monthcount = (SELECT DATEDIFF(MONTH, usr.registrationdate, GETDATE())
                      FROM   users usr
                      WHERE  usr.userid = @UserId);

SELECT @yearcount = (SELECT DATEDIFF(YEAR, usr.registrationdate, GETDATE())
                     FROM   users usr
                     WHERE  usr.userid = @UserId);

How to cast these 3 variables? Or how can I get this in one step?

no. of days / no. of months / no. of year

What I want the output is = 3:1:2 or 3 days / 1 month / 4 years

Are you using SQL Server **or** MySQL, and which version #? The question tags include both .... — SOS, Feb 27 '22 at 04:55
Assuming SQL Server, Does this answer your question? [SQL SELECT multi-columns INTO multi-variable](https://stackoverflow.com/questions/1340775/sql-select-multi-columns-into-multi-variable) — SOS, Feb 27 '22 at 05:19
See the link above (ignore the reference to Teradata). It shows how to set multiple variables in the same statement, ie `SELECT @datecount = Datediff(....), @monthcount = DateDiff(....), @yearcount = DateDiff(....) FROM users ... ` — SOS, Feb 27 '22 at 05:25
Are you trying to show how long a user has been registered with your application? You'll not see a result of 3 days/1 month/4 years using `datediff` calculations like that. If a user's registration date were `2021-02-27`, for example, you would get a result of 365 days/12 months/1 years because each calculation is independent of the others. — AlwaysLearning, Feb 27 '22 at 06:42
DATEDIFF will give you numbers that may not be what you want. For example 31-Dec-2020 to 1-Jan-2022 may yield 2 years, 13 months and 367 days. See [this question](https://stackoverflow.com/questions/57599/how-to-calculate-age-in-t-sql-with-years-months-and-days) for examples that would yield 1 years, 0 months and 1 day. — T N, Feb 27 '22 at 07:49
Also note that `DATEDIFF` counts date boundaries not whole years/months, so `DATEDIFF(year, '20211231', '20220101')` returns `1` — Charlieface, Feb 27 '22 at 11:32

LukStorms · Answer 1 · 2022-02-27T20:53:29.457

A user-defined function can be useful for this.
Since the correct calculation for such an interval isn't as straight forward as one would assume.

The UDF below calculates the interval between 2 dates, with the output in the format of a JSON object.

CREATE FUNCTION dbo.fnGetDateInterval
(
   @FromDate DATE, @ToDate DATE
)
RETURNS NVARCHAR(40)
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @Years INT, @Months INT, @Days INT;
  DECLARE @tmpFrom DATE;
  SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
      - IIF(@ToDate < DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate), @FromDate), 1, 0);
  SET @tmpFrom = DATEADD(YEAR, @Years , @FromDate)
  SET @Months =  DATEDIFF(MONTH, @tmpFrom, @ToDate)
      - IIF(@ToDate < DATEADD(MONTH,DATEDIFF(MONTH, @tmpFrom, @ToDate), @tmpFrom), 1, 0);
  SET @tmpFrom = DATEADD(MONTH, @Months , @tmpFrom)
  SET @Days =  DATEDIFF(DAY, @tmpFrom, @ToDate)
      - IIF(@ToDate < DATEADD(DAY, DATEDIFF(DAY, @tmpFrom, @ToDate), @tmpFrom), 1, 0);
  RETURN CONCAT('{', '"years":', @Years, 
                     ',"months":', @Months, 
                     ',"days":', @Days, '}')
END;

DECLARE @UserId INT = 42;

SELECT CONCAT(days, ' days ', months, ' months ', years, ' years') AS DaysMonthsYears
FROM users usr
CROSS APPLY OPENJSON(dbo.fnGetDateInterval(usr.registrationdate, GETDATE())) 
            WITH (years  INT '$.years',
                  months INT '$.months', 
                  days   INT '$.days') AS js
WHERE usr.userid = @UserId;

DaysMonthsYears

3 days 1 months 4 years

Test on db<>fiddle here

Get number of days,month,year from give two dates and type casting

1 Answers1