Passing a string representing date to postgres query in Ecto

Question

Trying to execute this postgres query:

select date('2015-10-05') from posts;

I'm passing the date as a string:

Ecto.Adapters.SQL.query!(
  Repo, "select date('$1') from posts", ["2015-10-05"])

But getting an error that the datetime format is invalid.

** (Postgrex.Error) ERROR (invalid_datetime_format): 
  invalid input syntax for type date: "$1"

[debug] select date('$1') from posts ["2015-10-05"]
  ERROR query=0.6ms
    (ecto) lib/ecto/adapters/sql.ex:172: Ecto.Adapters.SQL.query!/5

However, I'm not passing it as a date, but as a string. Postgres' date() function accepts string too, AFAIK and the error is still the same when I add ::varchar to make sure it's a string. Any advice how can this be avoided?

Since there's an Ecto.Date primitive (hexdocs.pm/ecto/Ecto.Date.html) what happens when you try that? — Onorio Catenacci
What happens when you do the query directly in the postgres cli, psql? — Patrick Oscity
@PatrickOscity if you mean, how does it work with substitution e.g. when 2015-10-05 is provided via arguments, I'm not sure how such query would look like. But otherwise it simply makes a query that selects the same date for all the posts — user2205259

bratsche bratsche · Accepted Answer · 2015-10-31T06:28:57

So you want to pass it into Ecto in the form {2015, 10, 5} instead of as a string.

Ecto.Adapters.SQL.query(Repo, "SELECT $1::date", [{2015, 10, 5}])

You can make a little helper function to turn a string into that format:

def string_to_date(str) do
  {_, date} = Ecto.Date.cast(str)
  Ecto.Date.to_erl(date)
end

Passing a string representing date to postgres query in Ecto

1 Answers