How to handle date in jooq?

Question

When we use a simple JDBC connection, we use the code below to format the date or convert the date

 if(argDB.equals("Oracle")){
       sb.append(" AND TO_CHAR(PaymentDate, 'YYYY-MM-DD') <= TO_CHAR(SYSDATE,'YYYY-MM-DD')");
          }
 else {
       sb.append(" AND CONVERT(VARCHAR(8), PaymentDate, 112) <= CONVERT(varchar(8), dbo.getdate(), 112)");
           }

Now we use JOOQ. Do you think we need to convert the date, as we did before, or JOOQcan handle this type of problem inside.? Since I checked right now the Oracle method is JOOQnot supported TO_CHARand Lukas gave some alternative to this here

Answer 1

Best way to write a predicate

, , , , , :

-- In SQL
ACCOUNT_PAYMENT.PAYMENT_DATE <= SYSDATE

// In jOOQ
ACCOUNT_PAYMENT.PAYMENT_DATE.le(DSL.currentDate())

TO_CHAR().

CustomField . jOOQ , SQL RenderContext, SQLDialect. ( API jOOQ 3.2):

class ToChar extends CustomField<String> {
    final Field<?> arg0;
    final Field<?> arg1;
    ToChar(Field<?> arg0, Field<?> arg1) {
        super("to_char", SQLDataType.VARCHAR);
        this.arg0 = arg0;
        this.arg1 = arg1;
    }
    @Override
    public void toSQL(RenderContext context) {
        context.visit(delegate(context.configuration()));
    }
    @Override
    public void bind(BindContext context) {
        context.visit(delegate(context.configuration()));
    }
    private QueryPart delegate(Configuration configuration) {
        switch (configuration.dialect().family()) {
            case ORACLE:
                return DSL.field("TO_CHAR({0}, {1})", 
                    String.class, arg0, arg1);

            case SQLSERVER:
                return DSL.field("CONVERT(VARCHAR(8), {0}, {1})", 
                    String.class, arg0, arg1);

            default:
                throw new UnsupportedOperationException("Dialect not supported");
        }
    }
}

:

public class MyDSL {
    public static Field<String> toChar(Field<?> field, String format) {
        return new ToChar(field, DSL.inline(format));
    }
}