How to create SQL using the "between" in Elixir Ecto

Question

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

I know how to create sql using like

where: like(t.descript, ^some_description)

But when I try to do it just like

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

I got an invalid msg error

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

How can I do it right?

Thanks in advance!

Answer 1

I do not think Ecto provides a between clause. You can complete your task using

 where: t.start_date >= ^start_date, where: t.start_date <= ^end_date 

Answer 2

You can use fragment for this.

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

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