Return Date Diff as Decimal

Question

I am trying to return the number of years between 2 dates as a decimal with greater precision.

For example:

If the difference is 1 year and 1 month and 15 days, I'd like to return a value of 1.15 or something like that. Ultimately, in this example, I'd like to show the difference is 1 year 1 month and 15 day difference shown in a decimal form.

I am able to get the decimal value to return, but I am unsure how to get the tenth and hundred decimal places to show properly. I'm sure I need to do some math to get that to show properly. Currently, my code just returns zero on the right side of the decimal place.

select 
     *,
     cast((cast(begin_date as date) - cast(end_date as date) YEAR) as decimal (3,2)) AS year_diff
from 
     x

Again, the expected results would be a value of 1.15 between 2 values that are 1 year, 1 month and 15 days apart. Currently I am only returning 1.00.

What SQL Server are you using? Also what are the 2 sample dates that I can use to test? — Stivan, Sep 05 '19 at 17:34
Check out the "Updated" solution on [this post](https://stackoverflow.com/a/1572411/7948962). You should be able to translate this to your query (which is currently nonfunctional so I can't really do that for you). But you should be able to copy this in your query and replace the variables with your columns. — Jacob H, Sep 05 '19 at 17:44
Hello @Stivan. I am using Teradata sql. I think 2 dates to use and test would be '2018-07-01' and '2019-08-16'. I am hoping to see a value returned like 1.15 or something approximately similar. Thank you. — bbal20, Sep 05 '19 at 17:50
Thank you for responding and the reference @JacobH. I'll take a look at that post and see if I can figure it out from there as well. — bbal20, Sep 05 '19 at 17:51
FYI: Thoroughly answering questions is time-consuming. If your question is solved by a presented solution(s), say thank you by checking one as accepted and or pushing the up arrow. Accept the solution that is best for your needs. The check is below the up/down arrow at the top left of the answer. **Leave a comment if it doesn't answer the question.** [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers). Thank you. — Trenton McKinney, Jul 13 '20 at 19:32

Stivan · Answer 1 · 2019-09-05T18:18:25.133

0

In the current select, you get number of days between the two dates and divide it by 365 (1 year).

Without Rounding decimal places:

select
    DATEDIFF(day, '2018-07-01', '2019-08-16')*1.0/365 as date;

Query Result: 1.126027

If you want to round to ONLY 2 decimal places:

select
    Round(DATEDIFF(day, '2018-07-01', '2019-08-16')*1.0/365,2) as date

Query Result: 1.13

Here is the sqlfiddle

edited Sep 05 '19 at 18:18

answered Sep 05 '19 at 18:11

Stivan

1,098
1
15
24

Andrew · Answer 2 · 2019-09-05T19:49:24.053

Having re-read your question, try this:

with cte (day1,day2) as(
select  date '2018-07-01' as day1,
date '2019-08-16' as day2)
select 
     cte.*,
     cast((cast(day2 as date) - cast(day1 as date) YEAR) as int) AS year_diff,
     cast (months_between(day2,day1)  as int) - (year_diff * 12) as month_diff,
     add_months(day2,(-1 * year_diff * 12 ) - month_diff) - day1 as days_diff
from 
    cte

+------------+------------+-----------+------------+-----------+
|    day1    |    day2    | year_diff | month_diff | days_diff |
+------------+------------+-----------+------------+-----------+
| 2018-07-01 | 2019-08-16 |         1 |          1 |        15 |
+------------+------------+-----------+------------+-----------+

Not entirely sure I understand exactly how you want to put all that together though...

score 0 · Answer 3 · answered Sep 05 '19 at 20:34

Your current Select returns years without fractional part.

In Teradata subtracting two dates returns the number of days inbetween, as every fourth year is a leap year this returns an approximate result:

cast((end_date - begin_date) / 365.25 as dec(6,2)) -- 1.13

This is usually better than using MONTH_BETWEEN which follows some strange Oracle logic :-)

cast(months_between(end_date, begin_date)/12 as dec(6,2))

Return Date Diff as Decimal

3 Answers3

Linked