Menu
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?
253
datediff returns an int, an the literal 60 also represents an int. So, just like others mentioned, changing the 60 to a numeric type by adding . 0 to it makes the output a numeric type, which includes the decimal places.
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.
This function accepts three parameters namely interval, first value of date, and second value of date.
DATEDIFF implicitly casts string literals as a datetime2 type. This means that DATEDIFF does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.
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.
57
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
