ADO FieldByName('X').AsCurrency returns 0

Question

Delphi XE8 and SQLServer 2017.

I have a query (TADOQuery) connected to a SQLServer database. After opening the dataset (TADOQuery.Open), if I call TADOQuery.FieldByName('X').AsCurrency it returns 0, but if I call TADOQuery.FieldByName('X').AsFloat it returns 12.65 (correct value). Looking at the specific field in the table, the type is numeric(18,4). What's wrong?

This code runs the otherthing:

with TADOQuery1 do
begin
  SQL.Clear;
  SQL.Add('select X from Table1');
  Open;
  if FieldByName('X').AsCurrency > 0 then  // <- Here is the problem
    do something
  else
    do otherthing;
end;

This code runs the something:

with TADOQuery1 do
begin
  SQL.Clear;
  SQL.Add('select X from Table1');
  Open;
  if FieldByName('X').AsFloat > 0 then  // <- Here is the problem
    do something
  else
    do otherthing;
end;

Answer 1

Cannot reproduce this behavior.

here is a MRE, amount is a numeric(18,4) in table Tbl_test :

program SO68004040;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  ActiveX,
  AdoDb,
  System.SysUtils;


var
  DbConn : TADOConnection;
  Qry    : TADOQuery;

begin
  Coinitialize(nil);
  try
    DbConn := TADOConnection.Create(nil);
    Qry := TADOQuery.Create(nil);
    try
     Qry.Connection := DbConn;
     DbConn.ConnectionString := 'Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=TestCustomer;Data Source=localhost\SQLEXPRESS;Integrated Security=SSPI; ';
     DbConn.Connected := True;
     Qry.SQL.Text := 'SELECT * FROM Tbl_test';
     Qry.Open;
     while not Qry.Eof do
      begin
       Writeln(Format('AsCurrency: %.4f', [Qry.FieldByName('amount').AsCurrency]));
       Writeln(Format('AsFloat: %.4f', [Qry.FieldByName('amount').AsFloat]));
       Qry.Next;
      end;
    finally
     Qry.Free;
     DbConn.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  CoUninitialize();
  Readln;
end.