{"record":{"id":"6ad9f7e227bafa61","repo":"hibernate/hibernate-orm","slug":"format-function-not-supported-on-mimer-sql","errorCode":null,"errorMessage":"format() function not supported on Mimer SQL","messagePattern":"format\\(\\) function not supported on Mimer SQL","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/MimerSQLDialect.java","lineNumber":328,"sourceCode":"\n\t@Override\n\tpublic LimitHandler getLimitHandler() {\n\t\treturn OffsetFetchLimitHandler.INSTANCE;\n\t}\n\n\t@Override\n\tpublic LockingSupport getLockingSupport() {\n\t\treturn LockingSupportSimple.NO_OUTER_JOIN;\n\t}\n\n\t@Override\n\tpublic boolean supportsOffsetInSubquery() {\n\t\treturn true;\n\t}\n\n\t@Override\n\tpublic void appendDatetimeFormat(SqlAppender appender, String format) {\n\t\tthrow new UnsupportedOperationException(\"format() function not supported on Mimer SQL\");\n\t}\n\n\t@Override\n\tpublic boolean useInputStreamToInsertBlob() {\n\t\treturn false;\n\t}\n\n\t@Override\n\tpublic boolean useConnectionToCreateLob() {\n\t\treturn false;\n\t}\n\n\t@Override\n\tpublic IdentityColumnSupport getIdentityColumnSupport() {\n\t\treturn MimerSQLIdentityColumnSupport.INSTANCE;\n\t}\n\n\t@Override","sourceCodeStart":310,"sourceCodeEnd":346,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/MimerSQLDialect.java#L310-L346","documentation":"MimerSQLDialect.appendDatetimeFormat() unconditionally throws: Mimer SQL has no date-to-string formatting function that Hibernate's datetime format pattern machinery could target, so the HQL/JPA format() datetime function is declared unsupported rather than silently producing wrong output.","triggerScenarios":"Executing an HQL query that uses the format() datetime function on Mimer SQL, e.g. \"select format(e.timestamp, 'yyyy-MM-dd') from Event e\" or JPA criteria format expressions; any code path that needs to render a datetime format pattern in SQL for this dialect.","commonSituations":"Report queries that format dates in SQL being pointed at a Mimer test/dev database; shared projections that build formatted date columns; migrating display-oriented queries from MySQL/Oracle profiles.","solutions":["Format on the Java side: select the raw timestamp and apply DateTimeFormatter.ofPattern in the mapper/projection","Use extract()-based expressions to build the pieces Mimer supports (year(?2), day(?2), ...) and concatenate them","Register a custom FunctionDescriptor ('format') via a MetadataBuilderContributor that implements formatting with Mimer functions if you need it in SQL"],"exampleFix":"// before\nList<String> days = session.createQuery(\n    \"select format(e.occurred, 'yyyy-MM-dd') from Event e\", String.class)\n    .getResultList(); // UnsupportedOperationException on Mimer\n\n// after - format in Java\nList<LocalDateTime> raw = session.createQuery(\n    \"select e.occurred from Event e\", LocalDateTime.class).getResultList();\nList<String> days = raw.stream()\n    .map(t -> t.format(DateTimeFormatter.ofPattern(\"yyyy-MM-dd\")))\n    .toList();","handlingStrategy":"validation","validationCode":"static boolean usesDatetimeFormat(String hql) {\n    return hql.toLowerCase().matches(\"(?s).*format\\\\s*\\\\(.*(?i:yyyy|mm|dd).*\\\\).*\");\n}\nif ( session.getJdbcServices().getDialect() instanceof MimerSQLDialect\n        && usesDatetimeFormat(hql) ) {\n    // select the raw timestamp instead and format with DateTimeFormatter in Java\n}","typeGuard":"static boolean isMimer(Dialect d) { return d instanceof MimerSQLDialect; }","tryCatchPattern":"try {\n    return session.createQuery(hql, String.class).getResultList();\n} catch (UnsupportedOperationException e) {\n    if ( String.valueOf(e.getMessage()).contains(\"format()\") ) {\n        // re-issue selecting raw timestamps and format client-side\n    }\n    throw e;\n}","preventionTips":["Format dates in the presentation layer, not in JPQL - it is portable across all dialects","If SQL-side formatting is required, encapsulate it in a dialect-specific native query or registered function","Smoke-test projection queries on every database profile your CI boots"],"tags":["hibernate","mimer-sql","datetime","format","dialect"],"backgroundTag":"datetime-format-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}