hibernate/hibernate-orm · error · UnsupportedOperationException

format() function not supported on Derby

Error message

format() function not supported on Derby

What it means

The HQL function format(datetime as 'pattern') is rendered through Dialect.appendDatetimeFormat, which converts the pattern to database-specific SQL. DerbyDialect never implements this hook - Derby has no equivalent of to_char/strftime - so any query containing format() throws UnsupportedOperationException during SQL rendering.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyDialect.java:746

						return new ConstraintViolationException(
								message,
								sqlException,
								sql,
								ConstraintViolationException.ConstraintKind.UNIQUE,
								constraintName
						);
					case "40XL1":
					case "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

  1. Format datetimes in Java: select the raw timestamp and apply DateTimeFormatter in the mapping layer
  2. Use Derby string functions manually through a native query, e.g. concatenating year(..)/month(..)/day(..) results
  3. Register a custom QueryFunction via FunctionContributions that implements the pattern in Derby SQL if you control the dialect contribution
  4. Run the affected queries only on databases whose dialect implements appendDatetimeFormat

Example fix

// before (HQL, throws on Derby)
List<String> labels = session.createQuery("select format(e.time as 'yyyy-MM-dd') from Event e", String.class).list();

// after (format in Java)
List<LocalDateTime> times = session.createQuery("select e.time from Event e", LocalDateTime.class).list();
List<String> labels = times.stream().map(t -> t.format(DateTimeFormatter.ISO_LOCAL_DATE)).toList();
Defensive patterns

Strategy: validation

Validate before calling

boolean isDerby = sessionFactory.getJdbcServices().getDialect() instanceof DerbyDialect;
if (isDerby && hql.contains("format(")) {
    throw new IllegalArgumentException("format() not supported on Derby - format in Java instead");
}

Type guard

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

Try / catch

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

Prevention

When it happens

Trigger: HQL like "select format(e.timestamp as 'YYYY-MM-DD') from Event e" or format() inside where/order clauses against Derby. The exception is thrown at query translation time, before any SQL reaches the database.

Common situations: Reusable JPQL report queries that must also run on Derby (often an embedded test/CI database); migrating an application to Derby and discovering datetime formatting has no equivalent; Criteria queries built with format() that run fine on PostgreSQL but fail on Derby.

Related errors

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