Datediff as decimal of a year

[goofaholix]

I'm inserting the following into a column that is data type float
(Datediff(dd,Date_Of_Start,isnull(DATE_OF_LEAVING,getdate()))/365) AS Length_of_Service

What I'm after is w decimal to several levels of precision that measures the years between start and finish but instead I'm getting an integer which is the number of full years as if I had done (Datediff(yy

Any idea what I'm doing wrong?

Thanks.

Bruce

 

[RiverGuy]

As much as I hate floating point numbers in databases:

(Datediff(dd,Date_Of_Start,isnull(DATE_OF_LEAVING,getdate()))/CONVERT(FLOAT, 365)) AS Length_of_Service

 

[philhege]

Put decimal places on the divisor (365) to the precision you need.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[SQLSister]

AS you can see, they solved your problem but I wanted to explain why the solutions worked. You see what you were running into was integer math. Whn you do math where all the items are datatype of integer (which is what 365 is and what datediff returns) then SQL Server assumes that you want an integer answer. TO fix, you need to make sure one of the parts of the calclution is not an integer.

Now float is a bad datatype to use if you expect to do calculations on a number as it is not an exact datatype. If at all possible, change the datatype to decimal for any floats you have in your database unless you want to introduce rounding and calculation errors into your data. (Set the decimal places to at least two more than your input values if you are planning to multiply or divide). Personally I would never use a float or real datatype. If I'm storing a number, I want to be able to do correct calculations on it.

"NOTHING is more important in a database than integrity." ESquared

 

[goofaholix]

Thanks for your replies, makes sense now. I'll look at changing the datatype to decimal as well but as I didn't create the table I can't be sure of the impacts.

Bruce