function to_char(unknown, unknown) is not unique

Question

When running the following the query.select * from surgicals where to_char(dt_surgery ,'DD-MM-YYYY' ) = to_char('12-02-2012','DD-MM-YYYY');

the error coming as 'SQL state 42725: ERROR: function to_char(unknown, unknown) is not unique'

How to run above select query?

Answer 1

You probably mean to_char('12-02-2012'::date, 'DD-MM-YYYY'). to_char cannot convert a plain string to string. Still, it does not seem to make sense, you need one of these two, depending on the format of your date constant (which cannot be determined from the actual example date you provided):

select * from surgicals where to_char(dt_surgery ,'DD-MM-YYYY' ) = '12-02-2012';
select * from surgicals where to_char(dt_surgery ,'MM-DD-YYYY' ) = '12-02-2012';

Answer 2

The wrongness here is that you're doing string comparison of dates. Use date/time math, which can take into account fun things like time zones etc. and still get it right.

Answer 3

Maybe this is what you need:

SELECT *
FROM surgicals 
WHERE date_trunc('day', dt_surgery) = '2012-02-12'
    ;