Problem with SQL server. Type a0.datetime is not a defined system type

Phoenix Forum Questions / Help
,
1

I was using this query

query =
from appoint in Appointment,
join: event in assoc(appoint, :event),
where: event.profile_id == ^profile.id and fragment(“?::date”, appoint.datetime) == ^date

To get a few rows matching the date from the database. This query was working correctly using postgresql database but after switching to SQL server I get the following error:

** (Tds.Error) Line 1 (Error 243): Type a0.datetime is not a defined system type.

2

Hey @Elder can you supply your ecto schema, as well as the migration used to create the table?

1 Like
3

:: is an operator in both databases’ SQL, but a totally different operator:

  • in Postgres it’s a Postgres-specific synonym for the CAST(expression AS type) operator in the SQL standard
  • in T-SQL it accesses elements of a composite type
3 Likes
4

Thanks a lot.