CODEWITHSUNDEEP
databasepostgresql
Stevie Weedy Lyngdoh
Stevie Weedy Lyngdoh

Reputation: 1

To_date function error based on Postgres database

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.

Upvotes: 0

Views: 7142

Answers (1)

user330315
user330315

Reputation:

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!

Upvotes: 1

Related Questions