{"record":{"id":"c2d0ff5516540d48","repo":"hibernate/hibernate-orm","slug":"format-function-not-supported-on-derby","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/DerbyDialect.java","lineNumber":746,"sourceCode":"\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\":\n\t\t\t\t\tcase \"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":728,"sourceCodeEnd":764,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyDialect.java#L728-L764","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Format datetimes in Java: select the raw timestamp and apply DateTimeFormatter in the mapping layer","Use Derby string functions manually through a native query, e.g. concatenating year(..)/month(..)/day(..) results","Register a custom QueryFunction via FunctionContributions that implements the pattern in Derby SQL if you control the dialect contribution","Run the affected queries only on databases whose dialect implements appendDatetimeFormat"],"exampleFix":"// before (HQL, throws on Derby)\nList<String> labels = session.createQuery(\"select format(e.time as 'yyyy-MM-dd') from Event e\", String.class).list();\n\n// after (format in Java)\nList<LocalDateTime> times = session.createQuery(\"select e.time from Event e\", LocalDateTime.class).list();\nList<String> labels = times.stream().map(t -> t.format(DateTimeFormatter.ISO_LOCAL_DATE)).toList();","handlingStrategy":"validation","validationCode":"boolean isDerby = sessionFactory.getJdbcServices().getDialect() instanceof DerbyDialect;\nif (isDerby && hql.contains(\"format(\")) {\n    throw new IllegalArgumentException(\"format() not supported on Derby - format in Java instead\");\n}","typeGuard":"static boolean supportsDatetimeFormat(Dialect d) {\n    return !(d instanceof DerbyDialect);\n}","tryCatchPattern":"try {\n    rows = session.createQuery(hql).list();\n} catch (UnsupportedOperationException e) {\n    if (e.getMessage().contains(\"format()\")) {\n        // re-run with raw timestamps selected, format in Java\n    } else throw e;\n}","preventionTips":["Keep string formatting out of queries - select raw temporals and format in the view/mapper layer","Add a dialect-capability check in a shared query helper so format() never reaches Derby","Document per-dialect function support next to shared report queries"],"tags":["hibernate","derby","hql","datetime-format","strftime","format-function"],"backgroundTag":"datetime-format-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}