sqlruby-on-railsfirebirdisqlquery

Raw SQL Query that causes error when translated to ActiveRecord Query (Firebird Database)

I am trying to do a fairly simply query that involves getting all the records between two dates via a specific column.

The raw SQL works fine in ISQL Firebird:

SELECT * FROM OPS_HEADER
WHERE PB_BOL_DT
BETWEEN '2020-09-01' AND '2020-09-10';

Here is my ActiveRecord Conversion:

OpsHeader.where('pb_bol_dt BETWEEN 2020-09-01 AND 2020-09-10')

This above line gives me this error:

expression evaluation not supported expression evaluation not supported Only one operand can be of type TIMESTAMP

I may be converting it wrong but it sure seems like this is the exact way to do it... I have no idea why it's giving me so much trouble.

Solution

  • You're missing quotes on the date literals, you'd want to start with:

    OpsHeader.where(%q(pb_bol_dt BETWEEN '2020-09-01' AND '2020-09-10'))

    But you can get ActiveRecord to build a BETWEEN by passing it a range:

    OpsHeader.where(pb_bol_dt: '2020-09-01' .. '2020-09-10')

    and letting AR deal with the quoting. You could also pass the end points separately using positional or named placeholders:

    OpsHeader.where('pb_bol_dt between ? and ?', '2020-09-01', '2020-09-10')
OpsHeader.where('pb_bol_dt between :lower and :upper', lower: '2020-09-01', upper: '2020-09-10')

    All of these will end up sending the same SQL to the database, the only difference is the small amount of string processing and type handling that is needed to build the latter three queries.