What causes "format() function not supported on Derby" in hibernate/hibernate-orm?

Select the raw datetime and format it in Java with DateTimeFormatter

hibernate/hibernate-orm · error · UnsupportedOperationException

format() function not supported on Derby

Error message

format() function not supported on Derby

What it means

format(datetime as 'pattern') in HQL is translated by Dialect.appendDatetimeFormat. DerbyLegacyDialect intentionally throws UnsupportedOperationException from that hook because Derby (and the SQL functions available to the legacy dialect) has no datetime-formatting facility to translate the pattern into.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyLegacyDialect.java:732

						constraintName = getViolatedConstraintNameExtractor().extractConstraintName(sqlException);
						return new ConstraintViolationException(
								message,
								sqlException,
								sql,
								ConstraintViolationException.ConstraintKind.UNIQUE,
								constraintName
						);
					case "40XL1", "40XL2":
						return new LockTimeoutException( message, sqlException, sql );
				}
			}
			return null;
		};
	}

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

	@Override
	protected void registerDefaultKeywords() {
		super.registerDefaultKeywords();
		registerKeyword( "ADD" );
		registerKeyword( "ALL" );
		registerKeyword( "ALLOCATE" );
		registerKeyword( "ALTER" );
		registerKeyword( "AND" );
		registerKeyword( "ANY" );
		registerKeyword( "ARE" );
		registerKeyword( "AS" );
		registerKeyword( "ASC" );
		registerKeyword( "ASSERTION" );
		registerKeyword( "AT" );
		registerKeyword( "AUTHORIZATION" );
		registerKeyword( "AVG" );

View on GitHub (pinned to fad1729dce)

Solutions

Select the raw datetime and format it in Java with DateTimeFormatter
Emulate the pattern with Derby string functions (year/month/day concatenation) through a native query
Migrate from DerbyLegacyDialect to DerbyDialect (still no format(), but current translator) and format outside SQL
Route queries needing format() to a database whose dialect implements appendDatetimeFormat

Example fix

// before (HQL, throws on Derby legacy dialect)
session.createQuery("from Event e where format(e.time as 'yyyy-MM') = :ym", Event.class)

// after (range predicate on the raw timestamp - also index-friendly)
YearMonth ym = YearMonth.parse(ymStr);
session.createQuery("from Event e where e.time >= :s and e.time < :e", Event.class)
    .setParameter("s", ym.atDay(1).atStartOfDay())
    .setParameter("e", ym.plusMonths(1).atDay(1).atStartOfDay())

Defensive patterns

Strategy: validation

Validate before calling

Dialect d = sessionFactory.getJdbcServices().getDialect();
if (d instanceof DerbyLegacyDialect && hql.contains("format(")) {
    hql = rewriteAsRangePredicate(hql); // format(t as 'yyyy-MM') = :x -> t >= start and t < end
}

Type guard

static boolean supportsFormat(Dialect d) {
    return !(d instanceof DerbyDialect || d instanceof DerbyLegacyDialect);
}

Try / catch

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

Prevention

Replace format()-equality filters with half-open range predicates on the raw timestamp - portable and sargable
Keep the legacy-Derby persistence unit on a reduced query set covered by smoke tests
Centralize date-literal building in one helper that knows the dialect's limits

When it happens

Trigger: Any HQL statement containing format() - e.g. "where format(e.time as 'yyyy-MM') = :ym" - executed while DerbyLegacyDialect is the active dialect. Fails during query translation (createQuery / prepare), before JDBC is involved.

Common situations: Applications that stayed on the legacy Derby dialect through a Hibernate upgrade; shared query libraries that assume format() exists because PostgreSQL/H2 support it; embedded Derby in desktop tools where the legacy dialect was pinned for compatibility.

Related errors

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