How to use toChar function in JOOQ?

Question

I have to use toChar() function in JOOQ? Right now i have used below code

 TO_CHAR(PaymentDate, 'YYYY-MM-DD') <= TO_CHAR(SYSDATE,'YYYY-MM-DD')");

Which i have to convert into JOOQ. How to use this in JOOQ?

Answer 1

Oracle's TO_CHAR() function is not explicitly supported by jOOQ 3.2. I have added a feature request for this: #2832.

In the mean time, you will have to resort to plain SQL as documented in the manual. For instance, you could write:

// Create reusable fields:
Field<String> f = DSL.field(
    "TO_CHAR({0}, 'YYYY-MM-DD')", String.class, T.PaymentDate);

// Create reusable conditions:
Condition c = DSL.condition(
    "TO_CHAR({0}, 'YYYY-MM-DD') <= TO_CHAR(SYSDATE, 'YYYY-MM-DD')", 
    T.PaymentDate);

Note that {0} is a reference to the first QueryPart argument of DSL.condition(String, QueryPart...), for instance.