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

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

1

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,128
1
15
24

score 1 · Answer 2 · 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))

Andrew · Answer 3 · 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...

Return Date Diff as Decimal

3 Answers3

Linked