I have a query like this:
Select a.*,b.event_code
from f.table1 a join f.table2 on a.id=b.id
where a.nos='807t'
and as_on_date=to_date (
(select event_date from f.table2 where nos='807t'), 'dd/mm/yyyy')
If I run this I get an error:
Function to_date (timestamp without time zone, unknown) does not exist.
Hint: no function matches the given name and argument types.
You might need to add explicit type casts.
If you want to convert a timestamp to a date, use a type cast:
Select a.*,b.event_code
from f.table1 a join f.table2 on a.id=b.id
where a.nos='807t'
and as_on_date = (select event_date::date
from f.table2
where nos='807t');
The syntax event_date::date is Postgres specific. If you want to use standard SQL, use cast(event_date as date).
The function to_date() should be used to convert a string (varchar) value to a date value.
Note that the above query will fail if table2.nos is not unique!