Getting a decimal value in query

[siituser]

I'm having to modify a Stored Procedure. I'm trying to calculate the number of days worked down to the decimal. So far, I've got :
Select ((DateDiff(d,'04/01/2004','04/29/2004') / 30) * 21)
(only more elegant)...

now according to my math,
((28/30)*21) = (0.93333 * 21) = 19.59 but I keep getting zeros. I tried to CAST it to a specific type and I can get decimal values as long as they are over 1 but under 1 (0.9333) comes back as zero

Any thoughts?

Thanks

 

[SQLSister]

Since you don't have any times there how can the date dif be between 0 and 1?

 

[NathanGriffiths]

You get zero's because the data types you are using are INTEGER and SQL Server is rounding the result.

Try

SELECT (DateDiff(d,'04/01/2004','04/29/2004') / 30.00) * 21

Because this divides by the decimal 30.00 and not an integer (30), SQL Server will implicitly convert the DateDiff result and the 21 value to decimal values to perfom the calculation and the result will be a decimal value;

-----------------------
19.599993

To return just two decimal places, try using CAST e.g.

SELECT Cast(((DateDiff(d,'04/01/2004','04/29/2004') / 30.00) * 21) AS DECIMAL(5,2))

Returns;

-------
19.60


Note that value is rounded.

Hope that helps,



Nathan