-1

I have a table that contains Id, Date and a float value as below:

ID      startDt                  Days
1328    2015-04-01 00:00:00.000 15
2444    2015-04-03 00:00:00.000 5.7
1658    2015-05-08 00:00:00.000 6
1329    2015-05-12 00:00:00.000 28.5
1849    2015-06-23 00:00:00.000 28.5
1581    2015-06-30 00:00:00.000 25.5
3535    2015-07-03 00:00:00.000 3
3536    2015-08-13 00:00:00.000 13.5
2166    2015-09-22 00:00:00.000 28.5
3542    2015-11-05 00:00:00.000 13.5
3543    2015-12-18 00:00:00.000 6
2445    2015-12-25 00:00:00.000 5.7
4096    2015-12-31 00:00:00.000 7.5
2446    2016-01-01 00:00:00.000 5.7
4287    2016-02-11 00:00:00.000 13.5
4288    2016-02-18 00:00:00.000 13.5
4492    2016-03-02 00:00:00.000 19.7
2447    2016-03-25 00:00:00.000 5.7

I am using a stored procedure which adds up the Days then subtracts it from a fixed value stored in a variable.

The total in the table is 245 and the variable is set to 245 so I should get a value of 0 when subtracting the two. However, I am getting a value of 5.6843418860808E-14 instead. I cant figure out why this is the case and I have gone and re entered each number in the table but I still get the same result.

This is my sql statement that I am using to calculate the result:

Declare @AL_Taken as float
Declare @AL_Remaining as float
Declare @EntitledLeave as float

Set @EntitledLeave=245

set @AL_Taken= (select sum(Days) from tblALMain)

Set @AL_Remaining=@EntitledLeave-@AL_Taken


Select @EntitledLeave, @AL_Taken, @AL_Remaining

The select returns the following: 245, 245, 5.6843418860808E-14

Can anyone suggest why I am getting this number when I should be getting 0?

Thanks for the help

Rob

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
spytek
  • 79
  • 1
  • 3
  • 16
  • 5
    5.6843418860808E-14 = 0.000000000000056843418860808. For most practical purposes, the value is 0. This is one of the vagaries of floating point arithmetic. – Gordon Linoff Dec 11 '15 at 17:17
  • 2
    Because float is not an exact data type, and you're getting a rounding error. Use decimal datatype and the problem should go away. – Tab Alleman Dec 11 '15 at 17:20
  • As @GordonLinoff states--this is an issue with floating point arithmetic. Is there any reason you aren't using integers (for whole days, as opposed to fractions of a day), which would avoid this problem? – Russ Dec 11 '15 at 17:22
  • @Russ Some might take 1/2 day off – Morpheus Dec 11 '15 at 17:32
  • 2
    If you want precision, dont' use float! If you need to save space and precisions isn't necessary use float. great comments on other stack questions relative to this: http://stackoverflow.com/questions/618535/difference-between-decimal-float-and-double-in-net http://stackoverflow.com/questions/19601975/storing-statistical-data-do-i-need-decimal-float-or-double Simply put: it's working as it should, you just have the wrong expectation. – xQbert Dec 11 '15 at 17:33
  • 1
    Someone is taking 5 days 16 hours 48 minutes off (=5.7 days)? Do you really need to calculate something on that precision? Maybe datetime would be better suited for that. – James Z Dec 11 '15 at 17:42
  • 1
    Look at using the DATEDIFF() function – Wes Palmer Dec 11 '15 at 18:02
  • Thanks for all the replies. This seems to be an isolated case since over 100 people use the application and its only this person who seems to have this figure. @Jamez: the column heading is a bit misleading. Some people have leave calculated in hours and the amount of time is entered by the user. Not sure why they entered 5.7. – spytek Dec 14 '15 at 09:13

1 Answers1

0

I changed the data type to Decimal as Tab Allenman suggested and this resolved my issue. I still dont understand why I didnt get zero when using float as all the values added up to 245 exactly (I even re-entered the values manually) and 245 - 245 should have given me 0.

Thanks again for all the comments and explanations.

Rob

spytek
  • 79
  • 1
  • 3
  • 16