Get month name from Date in jooq

Question

How can I generate the name of the month (e.g: Oct/October) from this date object in jooq?

date- "2019-11-01 00:00:00"

want to extract month name from date.

output- November

Thanks in advance.

Answer 1

You can extract the month number using DSL.month(Field).

jOOQ doesn't vendor specific support date time formatting functions out of the box. You could use plain SQL templating to use vendor specific functionality, such as:

// MySQL
public static Field<String> formatMonth(Field<Timestamp> dateColumn) {
  return DSL.field("date_format({0}, '%M')", SQLDataType.VARCHAR, dateColumn);
}

// PostgreSQL
public static Field<String> formatMonth(Field<Timestamp> dateColumn) {
  return DSL.field("to_char({0}, 'Month')", SQLDataType.VARCHAR, dateColumn);
}

If you need to do this several times and don't always want to worry about vendor agnosticity, you can create a CustomField:

public static Field<String> formatMonth(Field<Timestamp> dateColumn) {
  return new CustomField<String>("format_month", SQLDataType.VARCHAR) {
    @Override
    public void accept(Context<?> ctx) {
      switch (ctx.family()) {
        case MYSQL:
          ctx.visit(DSL.field("date_format({0}, '%M')", SQLDataType.VARCHAR, dateColumn));
          break;
        case POSTGRES:
          ctx.visit(DSL.field("to_char({0}, 'Month')", SQLDataType.VARCHAR, dateColumn));
          break;
        default:
          throw new UnsupportedOperationException("Dialect not supported: " + ctx.family());
      }
    }
  };
}