Could not choose a best candidate function. You might need to add explicit type casts

Question

select
  c_elementvalue.value AS "VALUE",
  c_elementvalue.name AS "NAME",
  rv_fact_acct.postingtype AS "POSTINGTYPE",
  sum(rv_fact_acct.amtacct) AS "AMNT",
  'YTDB' AS "TYPE",
  c_period.enddate AS "ENDDATE",
  max(ad_client.description) AS "COMPANY"
from
  adempiere.c_period,
  adempiere.rv_fact_acct,
  adempiere.c_elementvalue,
  adempiere.ad_client
where
  (rv_fact_acct.ad_client_id =  ad_client.ad_client_id ) and
  (rv_fact_acct.c_period_id = c_period.c_period_id) and
  (rv_fact_acct.account_id = c_elementvalue.c_elementvalue_id) and
  (rv_fact_acct.dateacct BETWEEN  to_date( to_char( '2017-03-01' ,'YYYY') ||'-04-01', 'yyyy-mm-dd')  AND '2017-03-31'  ) AND
  (rv_fact_acct.ad_client_id = 1000000) and
  (rv_fact_acct.c_acctschema_id = 1000000 )and
  (rv_fact_acct.postingtype = 'B')and
  (rv_fact_acct.accounttype in ('R','E') )
group by  c_elementvalue.value , c_elementvalue.name , rv_fact_acct.postingtype , c_period.enddate
order by 5 asc, 1 asc

I got an error message, when executing above sql statement(postgres).

Error message:

[Err] ERROR:  function to_char(unknown, unknown) is not unique
LINE 68:  (rv_fact_acct.dateacct BETWEEN  to_date( to_char( '2017-03-...
                                                   ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts. 

Answer 1

This part of your query is problematic:

to_date( to_char( '2017-03-01' ,'YYYY') ||'-04-01', 'yyyy-mm-dd')

There are not any function to_char, that has first parameter string.

postgres=# \df to_char
                                   List of functions
┌────────────┬─────────┬──────────────────┬───────────────────────────────────┬────────┐
│   Schema   │  Name   │ Result data type │        Argument data types        │  Type  │
╞════════════╪═════════╪══════════════════╪═══════════════════════════════════╪════════╡
│ pg_catalog │ to_char │ text             │ bigint, text                      │ normal │
│ pg_catalog │ to_char │ text             │ double precision, text            │ normal │
│ pg_catalog │ to_char │ text             │ integer, text                     │ normal │
│ pg_catalog │ to_char │ text             │ interval, text                    │ normal │
│ pg_catalog │ to_char │ text             │ numeric, text                     │ normal │
│ pg_catalog │ to_char │ text             │ real, text                        │ normal │
│ pg_catalog │ to_char │ text             │ timestamp without time zone, text │ normal │
│ pg_catalog │ to_char │ text             │ timestamp with time zone, text    │ normal │
└────────────┴─────────┴──────────────────┴───────────────────────────────────┴────────┘
(8 rows)

You can cast string 2017-03-01 to date type. PostgreSQL cannot do it self, because there are more variants: numeric,timestamp, ...

postgres=# select to_date( to_char( '2017-03-01'::date ,'YYYY') ||'-04-01', 'yyyy-mm-dd');
┌────────────┐
│  to_date   │
╞════════════╡
│ 2017-04-01 │
└────────────┘
(1 row)

Usually, using string operations for date time operations are wrong. PostgreSQL (and all SQL databases) has great functions for date arithmetic.

For example - the task "get first date of following month" can be done with expression:

postgres=# select date_trunc('month', current_date + interval '1month')::date;
┌────────────┐
│ date_trunc │
╞════════════╡
│ 2017-05-01 │
└────────────┘
(1 row)

You can write custom function in SQL language (macro):

postgres=# create or replace function next_month(date) 
           returns date as $$
             select date_trunc('month', $1 + interval '1month')::date $$
           language sql;
CREATE FUNCTION
postgres=# select next_month(current_date);
┌────────────┐
│ next_month │
╞════════════╡
│ 2017-05-01 │
└────────────┘
(1 row)