CODEWITHSUNDEEP
sql-server-2008t-sqldatediff
chrissy p
chrissy p

Reputation: 823

DateDiff years into decimals

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?

Upvotes: 9

Views: 24572

Answers (4)

Davor
Davor

Reputation: 129

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)

Upvotes: 0

Isaac Fratti
Isaac Fratti

Reputation: 535

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))

Upvotes: 3

Diego
Diego

Reputation: 21

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

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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.

Upvotes: 22

Related Questions