Between Clause with Value "between" Two Fields?

[tkofford]

How would you do a between clause where the value is the target and the fields are the params to the between function. So instead of the standard:
where(field(some_field).between(start_date_value, end_date_value)
it's reversed so that the value is between 2 query fields as follows:
where(date_value between(field(start_field), field(end_Field)))

[fbraem]

This doesn't work with sql-smith. The best way is to change the query as follows:

where start_field <= date_value and end_field >= date_value

[tkofford]

OK, is there any way to make this a new feature request? It's not something we do a lot, but it's definitely something that will keep coming up in our work.

[fbraem]

After a closer look: You can use CriteriaBuilder and identify like this:

    def test_between_columns(self) -> None:
        criteria = CriteriaBuilder("2020-01-01").between(
            identify("start_date"), identify("end_date")
        )
        sql = self._factory.select().from_("employees").where(criteria)
        self.assertSql(
            "SELECT * FROM employees WHERE ? BETWEEN start_date AND end_date",
            sql,
        )