CODEWITHSUNDEEP
javamysqlsqljooq
Mr.Java
Mr.Java

Reputation: 389

SQL DATE_FORMAT in Alternative in Jooq

I'm using 3.5 jooq version

I know that jooq don't support DATE_FORMAT function but what are the alternatives

This is the query that i want to create with JOOQ 

SELECT DATE_FORMAT(`date_create`, '%d/%m/%Y') AS date_create FROM users
GROUP BY DATE_FORMAT(`date_create`, '%d/%m/%Y')

Upvotes: 3

Views: 2721

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

Whenever you need a vendor-specific SQL feature that is not supported by jOOQ out of the box, the plain SQL API will be your friend. Write a utility as such:

public static Field<String> dateFormat(Field<Date> field, String format) {
    return DSL.field("date_format({0}, {1})", SQLDataType.VARCHAR, 
        field, DSL.inline(format));
}

You can then use it like any other jOOQ-provided function:

DSL.using(configuration)
   .select(dateFormat(USERS.DATE_CREATE, "%d/%m/%Y").as("date_create"))
   .from(USERS)
   .groupBy(dateFormat(USERS.DATE_CREATE, "%d/%m/%Y"))
   .fetch();

Upvotes: 10

Related Questions