hibernate/hibernate-orm · error · UnsupportedOperationException

format() function not supported on Firebird

Error message

format() function not supported on Firebird

What it means

The HQL format() function (format(datetime as 'pattern')) is rendered via Dialect.appendDatetimeFormat. FirebirdDialect does not implement that hook - Firebird has no built-in datetime-to-string formatting function - so any HQL containing format() fails with UnsupportedOperationException during SQL generation.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/FirebirdDialect.java:899

				break;
			case TIME:
				appender.appendSql( "time '" );
				appendAsLocalTime( appender, calendar );
				appender.appendSql( '\'' );
				break;
			case TIMESTAMP:
				appender.appendSql( "timestamp '" );
				appendAsTimestampWithMillis( appender, calendar, jdbcTimeZone );
				appender.appendSql( '\'' );
				break;
			default:
				throw new IllegalArgumentException();
		}
	}

	@Override
	public void appendDatetimeFormat(SqlAppender appender, String format) {
		throw new UnsupportedOperationException( "format() function not supported on Firebird" );
	}

	@Override
	public void appendUUIDLiteral(SqlAppender appender, UUID literal) {
		appender.appendSql( "char_to_uuid('" );
		appender.appendSql( literal.toString() );
		appender.appendSql( "')" );
	}

	@Override
	public ViolatedConstraintNameExtractor getViolatedConstraintNameExtractor() {
		return EXTRACTOR;
	}

	private static final Pattern FOREIGN_UNIQUE_OR_PRIMARY_KEY_PATTERN =
			Pattern.compile( "violation of .+? constraint \"([^\"]+)\"" );
	private static final Pattern CHECK_CONSTRAINT_PATTERN =
			Pattern.compile( "Operation violates CHECK constraint (.+?) on view or table" );

View on GitHub (pinned to fad1729dce)

Solutions

  1. Format in Java: fetch the raw timestamp and apply DateTimeFormatter
  2. Use a native Firebird query with EXTRACT(YEAR/MONTH/DAY FROM ...) concatenation for the rare cases formatting must happen in SQL
  3. Register a Firebird UDF or stored function via FunctionContributions and call it with function('fmt_date', e.eventTime)
  4. Keep format()-dependent queries off the Firebird profile

Example fix

// before (HQL, throws on Firebird)
session.createQuery("select format(f.paidAt as 'dd/MM/yyyy') from Invoice f", String.class).list();

// after (format in Java)
List<LocalDate> paid = session.createQuery("select f.paidAt from Invoice f", LocalDate.class).list();
List<String> labels = paid.stream().map(d -> d.format(DateTimeFormatter.ofPattern("dd/MM/yyyy"))).toList();
Defensive patterns

Strategy: validation

Validate before calling

Dialect d = sessionFactory.getJdbcServices().getDialect();
if (d instanceof FirebirdDialect && hql.contains("format(")) {
    throw new UnsupportedOperationException("format() is not available on Firebird; format in Java");
}

Type guard

static boolean supportsDatetimeFormat(Dialect d) {
    return !(d instanceof FirebirdDialect);
}

Try / catch

try {
    rows = session.createQuery(hql).list();
} catch (UnsupportedOperationException e) {
    if (e.getMessage().contains("format()")) { /* select raw timestamps, format in Java */ } else throw e;
}

Prevention

When it happens

Trigger: HQL such as "select format(e.eventTime as 'YYYY-MM') from Event e" or a where/order predicate built with format() when the FirebirdDialect is active. The error occurs in the translator (at createQuery/prepare time), before SQL is sent to Firebird.

Common situations: Dashboard/report JPQL shared across databases that breaks only on Firebird; migrating an application from PostgreSQL (to_char-backed format()) to Firebird; Criteria dynamic queries that assume format() portability.

Related errors

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/cb42f685e8496513. Report an issue: GitHub.