Can we select without a table in ecto?

I am working with MS SQL server and I am trying to build a complex query with ecto which contains a CTE:

	timeslots AS
		SELECT cast('2021-01-09T00:00:00' as smalldatetime) as slot

		SELECT dateadd(minute , 1, slot)
		FROM timeslots
		WHERE dateadd(minute, 1, slot) < '2021-01-15T00:00:00'

After reading the documentation for CTE with Ecto, I was trying to build the “initial query” but I did not get far.

Indeed, I do not see how I can select a value not coming from a table (e.g.: SELECT cast('2021-01-09T00:00:00' as smalldatetime) as slot).

Is there a way to do that without using fragments?

sure you can but haven’t tested nested, inner queries but cast and rest can be done.

def update_title(post, new_title) do
  query =
    from "posts",
      where: [id: ^],
      update: [set: [title: ^new_title]]

  MyApp.Repo.update_all(query, [])

More examples can be found at

1 Like

Btw if you are dealing with datetime, please check out these before investing unnecessary time into fragments() like I did


Ecto supports following values for interval option: "year", "month", "week", "day", "hour", "minute", "second", "millisecond", and "microsecond".

Date/Time functions like datetime_add/3, date_add/3, from_now/2, ago/2 take interval as an argument.

All of these will execute on the SQL server and use the native underlying functions


Wow thanks for sharing, I had been using Timex for this purpose but built-in solution is great!

Thanks for your reply but it does not answer my question. Perhaps I did not explain correctly.

How would you write SELECT 1 with Ecto? Here I select a simple literal and it is not done over any table.

Ecto.Adapters.SQL.query(MyRepo, "SELECT $1::integer + $2", [40, 2])
{:ok, %{rows: [[42]], num_rows: 1}}
1 Like

Once again, I did not explain correctly. Sorry for that.

In my initial question, I wrongly wrote “without using fragments”. What I meant was “without using raw SQL”.

I’m not going to answer your question again :smile:

In the sample above, I’d personally opt to “inject” the empty slot once the results have come back from the database. Adding to the front of a list in Elixir is a very “cheap” operation.


  slots = [%{slot: ~D[<whatever date>]} | Repo.all(more_basic_query)]

I suspect the situation you are asking about (effectively “UNION”-ing a hand crafted row of data with database results) doesn’t fit within the Ecto model. I had another look through the documentation and I can’t see anything that comes close. So you will either need to use a fragment, or go for the option I noted above, or build a view that performs the UNION on the database side.


No, there currently is no way to use Ecto.Query with no or non-relation FROM (so no stored procedures for you either).

1 Like