DateDiff years into decimals

Question

I have two dates in which i would like to find the number of years between them, however i would need to show the value to two decimal places. I have tried the following but i always get a value returned of 0 as all of my dates do not cover a whole year:

DATEDIFF(yy, @EndDateTime, i.mat_exp_dte)

I have then tried finding the number of days between the two and then dividing it by 365, but this still returns 0:

DATEDIFF(dd, @EndDateTime, i.mat_exp_dte)/365

Am confused now as to how to calculate this. Would i need to convert the DataDiff into a different data type?

score 22 · Accepted Answer · answered May 02 '12 at 10:16

22

Try this instead.

DATEDIFF(dd, @EndDateTime, i.mat_exp_dte)/365.0

Dividing int with an int returns int. Divide with a decimal and you will get a decimal as a result.

answered May 02 '12 at 10:16

Mikael Eriksson

136,425
22
210
281

what is the command in sql to cut(! - not round) decimal places ? (lets say 2) ? 1.12345 --->1.12 – Royi Namir May 02 '12 at 10:17
@RoyiNamir - Last try :) `select round(1.19999, 2, 1)` – Mikael Eriksson May 02 '12 at 10:27
select CAST ( round(1.19999, 2, 1) AS numeric(3, 2))........but i think its too long...:) – Royi Namir May 02 '12 at 10:29
1

This wouldn't take in to account leap years. Its a small adjustment but would still need to be factored in. – Matt Bartlett Jan 18 '19 at 11:02

score 3 · Answer 2 · answered Oct 17 '18 at 14:08

3

If you want a more accurate version of this that accounts for leap years, then you can do this instead:

cast(DATEDIFF(dd, @EndDateTime, i.mat_exp_dte) as float)
/datediff(dd,@EndDateTime,dateadd(yy,1,@EndDateTime))

answered Oct 17 '18 at 14:08

Isaac Fratti

475
1
4
8

Great answer! This is what I was looking for several times. Glad I found it. To everyone: don't do stuff like dividing by 365.2524 because it is not right. This answer is. – GerardV Apr 28 '23 at 10:33

score 2 · Answer 3 · answered Nov 18 '12 at 11:25

2

.0 stands for floating point number, hence calculations include decimals. Without it you calculate integer division.

answered Nov 18 '12 at 11:25

Diego

21
1

Davor · Answer 4 · 2019-05-30T11:49:27.450

0

SELECT
(YEAR(@EndDte) - YEAR(@StartDate) + 
(MONTH(@EndDate) - MONTH(@StartDate) /100.0 + 
(DAY(@EndDate) - DAY(@StartDate) /10000.0)

If you need to determinate if there is exactly one year, less then one, or more than one. (=1, <1, >1)

edited May 30 '19 at 11:49

answered May 28 '19 at 08:42

Davor

129
1
5

DateDiff years into decimals

4 Answers4