How to create a SQL using 'between' in Elixir Ecto

Question

I want to create a SQL using the keywork 'between' in Elixir Ecto.

I know how to create a sql using like

where: like(t.descript, ^some_description)

But when I try to do it in the same way as like

where: between(t.start_date, ^start_date, ^end_date),

I got the "not valid" error msg

** (Ecto.Query.CompileError) `between(t.start_date(), ^start_date, ^end_date)` is not a valid query expression.**

How can I do it the right way?

Thanks in advance!!

Answer 1

You can use fragment to do this.

where: fragment("? BETWEEN ? AND ?", t.date, ^start_date, ^end_date)

https://hexdocs.pm/ecto/3.1.4/Ecto.Query.API.html#fragment/1