{"record":{"id":"2f3f0c25fd71b8a4","repo":"hibernate/hibernate-orm","slug":"format-function-not-supported-on-derby-2f3f0c","errorCode":null,"errorMessage":"format() function not supported on Derby","messagePattern":"format\\(\\) function not supported on Derby","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyLegacyDialect.java","lineNumber":732,"sourceCode":"\t\t\t\t\t\tconstraintName = getViolatedConstraintNameExtractor().extractConstraintName(sqlException);\n\t\t\t\t\t\treturn new ConstraintViolationException(\n\t\t\t\t\t\t\t\tmessage,\n\t\t\t\t\t\t\t\tsqlException,\n\t\t\t\t\t\t\t\tsql,\n\t\t\t\t\t\t\t\tConstraintViolationException.ConstraintKind.UNIQUE,\n\t\t\t\t\t\t\t\tconstraintName\n\t\t\t\t\t\t);\n\t\t\t\t\tcase \"40XL1\", \"40XL2\":\n\t\t\t\t\t\treturn new LockTimeoutException( message, sqlException, sql );\n\t\t\t\t}\n\t\t\t}\n\t\t\treturn null;\n\t\t};\n\t}\n\n\t@Override\n\tpublic void appendDatetimeFormat(SqlAppender appender, String format) {\n\t\tthrow new UnsupportedOperationException(\"format() function not supported on Derby\");\n\t}\n\n\t@Override\n\tprotected void registerDefaultKeywords() {\n\t\tsuper.registerDefaultKeywords();\n\t\tregisterKeyword( \"ADD\" );\n\t\tregisterKeyword( \"ALL\" );\n\t\tregisterKeyword( \"ALLOCATE\" );\n\t\tregisterKeyword( \"ALTER\" );\n\t\tregisterKeyword( \"AND\" );\n\t\tregisterKeyword( \"ANY\" );\n\t\tregisterKeyword( \"ARE\" );\n\t\tregisterKeyword( \"AS\" );\n\t\tregisterKeyword( \"ASC\" );\n\t\tregisterKeyword( \"ASSERTION\" );\n\t\tregisterKeyword( \"AT\" );\n\t\tregisterKeyword( \"AUTHORIZATION\" );\n\t\tregisterKeyword( \"AVG\" );","sourceCodeStart":714,"sourceCodeEnd":750,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyLegacyDialect.java#L714-L750","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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"],"exampleFix":"// before (HQL, throws on Derby legacy dialect)\nsession.createQuery(\"from Event e where format(e.time as 'yyyy-MM') = :ym\", Event.class)\n\n// after (range predicate on the raw timestamp - also index-friendly)\nYearMonth ym = YearMonth.parse(ymStr);\nsession.createQuery(\"from Event e where e.time >= :s and e.time < :e\", Event.class)\n    .setParameter(\"s\", ym.atDay(1).atStartOfDay())\n    .setParameter(\"e\", ym.plusMonths(1).atDay(1).atStartOfDay())","handlingStrategy":"validation","validationCode":"Dialect d = sessionFactory.getJdbcServices().getDialect();\nif (d instanceof DerbyLegacyDialect && hql.contains(\"format(\")) {\n    hql = rewriteAsRangePredicate(hql); // format(t as 'yyyy-MM') = :x -> t >= start and t < end\n}","typeGuard":"static boolean supportsFormat(Dialect d) {\n    return !(d instanceof DerbyDialect || d instanceof DerbyLegacyDialect);\n}","tryCatchPattern":"try {\n    list = session.createQuery(hql, String.class).list();\n} catch (UnsupportedOperationException e) {\n    if (e.getMessage().contains(\"format()\")) { /* select raw temporal, format in Java */ } else throw e;\n}","preventionTips":["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"],"tags":["hibernate","derby","legacy-dialect","hql","datetime-format","format-function"],"backgroundTag":"datetime-format-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}