Why Does Clng Work Differently In These Scenarios And Can It Be Reproduced In SQL Server? (Not Banker's Rounding)

Question

Executing the following statement results in Access SQL:

CLNG((CCUR(1.225)/1)*100) = 123

The Conversion Goes, Decimal > Currency > Double > Double > Long

If I remove the CCUR conversion function:

CLNG(((1.225)/1)*100) = 122

The Conversion here goes , Decimal > Double > Double > Long

What is the difference between these two?

This extends to being different between Code And Access SQL

In Access SQL

 clng((CCUR(1.015)/1)*100)/100 = 1.01 (Wrong Rounding)

In Access VBA

 clng((CCUR(1.015)/1)*100)/100 = 1.02 (Appropriate Rounding Here)

Microsoft explain that the CLng function uses Banker's Rounding, here.

When the fractional part is exactly 0.5, CInt and CLng always round it to the nearest even number. For example, 0.5 rounds to 0, and 1.5 rounds to 2. CInt and CLng differ from the Fix and Int functions, which truncate, rather than round, the fractional part of a number. Also, Fix and Int always return a value of the same type as is passed in.

Looking at a similar question and the subsequent answer HERE, it explains that there are changes to the bit calculation behind the scenes, based on how it is calculated, but I'm not sure how the data type effects it.

What am I missing, and why is it calculating this way? How could I reproduce this behavior predictably in SQL Server?

EDIT

After some digging I believe that this is truly the result of a rounding point issue. In SQL server it will round floats to the nearest whole number if it is outside of the 15 digit max of precision. Access seems to hold more somehow, even though a Double is equivalent to a Float(53) in TSQL.

Answer 1

The difference in results is a combination of two different issues: Jet/ACE vs VBA expression evaluation and binary floating point representation of decimal numbers.

The first is that the Jet/ACE expression engine implicitly converts fractional numbers to Decimal while VBA converts them to Double. This can be easily demonstrated (note the Eval() function evaluates an expression using the Jet/ACE db engine):

?Typename(1.015), eval("typename(1.015)")
Double        Decimal

The second issue is that of floating point arithmetic. This is somewhat more difficult to demonstrate because VBA always rounds its output, but the issue is more obvious using another language (Python, in this case):

>>> from decimal import Decimal
>>> Decimal(1.015)
Decimal('1.0149999999999999023003738329862244427204132080078125')

The Double type in VBA uses floating-point arithmetic, while the Decimal type uses integer arithmetic (it stores the position of the decimal point behind the scenes).

The upshot to this is that Banker's rounding or traditional rounding is a red herring. The determining factor is whether the binary floating point representation of the number is slightly greater or less than its decimal representation.

---

To see how this works in your original question see the following VBA:

?Eval("typename((CCUR(1.225)/1))"), Eval("typename(((1.225)/1))")
Double        Decimal
?Eval("typename(CCUR(1.225))"), Eval("typename(1.225)") 
Currency      Decimal

And Python:

>>> Decimal(1.225)
Decimal('1.225000000000000088817841970012523233890533447265625')

---

I should also point out that your assumption of the conversion to Double in your second example is incorrect. The data type remains Decimal until the final conversion to Long. The difference between the first two functions is that multiplying a Decimal by a Currency type in Jet/ACE results in a Double. This seems like somewhat odd behavior to me, but the code bears it out:

?eval("TypeName(1.225)"), eval("TypeName(1.225)")
Decimal       Decimal

?eval("TypeName(CCUR(1.225))"), eval("TypeName((1.225))")
Currency      Decimal

?eval("TypeName(CCUR(1.225)/1)"), eval("TypeName((1.225)/1)")
Double        Decimal

?eval("TypeName((CCUR(1.225)/1)*100)"), eval("TypeName(((1.225)/1)*100)")
Double        Decimal

?eval("TypeName(CLNG((CCUR(1.225)/1)*100))"), eval("TypeName(CLNG(((1.225)/1)*100))")
Long          Long

So the conversion in the two cases is actually:

Decimal > Currency > Double > Double > Long (as you correctly assumed); and

Decimal > Decimal > Decimal > Decimal > Long (correcting your initial assumption).

---

To answer your question in the comment below, Eval() uses the same expression engine as Jet/ACE, so it is functionally equivalent to entering the same formula in an Access query. For further proof, I present the following:

SELECT
TypeName(1.225) as A1,
TypeName(CCUR(1.225)) as A2, 
TypeName(CCUR(1.225)/1) as A3,
TypeName((CCUR(1.225)/1)*100) as A4, 
TypeName(CLNG((CCUR(1.225)/1)*100)) as A5

SELECT
TypeName(1.225) as B1,
TypeName((1.225)) as B2,
TypeName((1.225)/1) as B3,
TypeName(((1.225)/1)*100) as B4,
TypeName(CLNG(((1.225)/1)*100)) as B5