{"record":{"id":"cb42f685e8496513","repo":"hibernate/hibernate-orm","slug":"format-function-not-supported-on-firebird","errorCode":null,"errorMessage":"format() function not supported on Firebird","messagePattern":"format\\(\\) function not supported on Firebird","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/FirebirdDialect.java","lineNumber":899,"sourceCode":"\t\t\t\tbreak;\n\t\t\tcase TIME:\n\t\t\t\tappender.appendSql( \"time '\" );\n\t\t\t\tappendAsLocalTime( appender, calendar );\n\t\t\t\tappender.appendSql( '\\'' );\n\t\t\t\tbreak;\n\t\t\tcase TIMESTAMP:\n\t\t\t\tappender.appendSql( \"timestamp '\" );\n\t\t\t\tappendAsTimestampWithMillis( appender, calendar, jdbcTimeZone );\n\t\t\t\tappender.appendSql( '\\'' );\n\t\t\t\tbreak;\n\t\t\tdefault:\n\t\t\t\tthrow new IllegalArgumentException();\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 Firebird\" );\n\t}\n\n\t@Override\n\tpublic void appendUUIDLiteral(SqlAppender appender, UUID literal) {\n\t\tappender.appendSql( \"char_to_uuid('\" );\n\t\tappender.appendSql( literal.toString() );\n\t\tappender.appendSql( \"')\" );\n\t}\n\n\t@Override\n\tpublic ViolatedConstraintNameExtractor getViolatedConstraintNameExtractor() {\n\t\treturn EXTRACTOR;\n\t}\n\n\tprivate static final Pattern FOREIGN_UNIQUE_OR_PRIMARY_KEY_PATTERN =\n\t\t\tPattern.compile( \"violation of .+? constraint \\\"([^\\\"]+)\\\"\" );\n\tprivate static final Pattern CHECK_CONSTRAINT_PATTERN =\n\t\t\tPattern.compile( \"Operation violates CHECK constraint (.+?) on view or table\" );","sourceCodeStart":881,"sourceCodeEnd":917,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/FirebirdDialect.java#L881-L917","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Format in Java: fetch the raw timestamp and apply DateTimeFormatter","Use a native Firebird query with EXTRACT(YEAR/MONTH/DAY FROM ...) concatenation for the rare cases formatting must happen in SQL","Register a Firebird UDF or stored function via FunctionContributions and call it with function('fmt_date', e.eventTime)","Keep format()-dependent queries off the Firebird profile"],"exampleFix":"// before (HQL, throws on Firebird)\nsession.createQuery(\"select format(f.paidAt as 'dd/MM/yyyy') from Invoice f\", String.class).list();\n\n// after (format in Java)\nList<LocalDate> paid = session.createQuery(\"select f.paidAt from Invoice f\", LocalDate.class).list();\nList<String> labels = paid.stream().map(d -> d.format(DateTimeFormatter.ofPattern(\"dd/MM/yyyy\"))).toList();","handlingStrategy":"validation","validationCode":"Dialect d = sessionFactory.getJdbcServices().getDialect();\nif (d instanceof FirebirdDialect && hql.contains(\"format(\")) {\n    throw new UnsupportedOperationException(\"format() is not available on Firebird; format in Java\");\n}","typeGuard":"static boolean supportsDatetimeFormat(Dialect d) {\n    return !(d instanceof FirebirdDialect);\n}","tryCatchPattern":"try {\n    rows = session.createQuery(hql).list();\n} catch (UnsupportedOperationException e) {\n    if (e.getMessage().contains(\"format()\")) { /* select raw timestamps, format in Java */ } else throw e;\n}","preventionTips":["Keep formatting in the application layer, not in shared HQL","Maintain a per-dialect function whitelist in the query builder","Test report queries on every supported backend before release"],"tags":["hibernate","firebird","hql","datetime-format","format-function","to-char"],"backgroundTag":"datetime-format-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}