SQL - using date range

I am trying to port existing SQL to Opendistro and struggling with using date ranges.
When I fire a query with "WHERE date_format(my_date_field, ‘yyyy-MM-dd’) > '2021-01-01’, everything works fine. However, if I add "AND date_format(my_date_firld, ‘yyyy-MM-dd’) < '2021-01-31’ ES simply returns nothing - no errors or exceptions.

Appreciate everyone in advance for your knowledge.

I am currently testing with ES v7.9 and v.7.10


Hi @unseokyeo , could you provide more info about your index mapping and data? So we can reproduce the issue from our side. Thanks!