# Hibernate 6 - Criteria 'hour' is not a recognized built-in function name

**URL:** <https://discourse.hibernate.org/t/hibernate-6-criteria-hour-is-not-a-recognized-built-in-function-name/8738>\
**Category:** Hibernate ORM\
**Created:** [December 22, 2023, 5:26pm UTC](https://discourse.hibernate.org/t/hibernate-6-criteria-hour-is-not-a-recognized-built-in-function-name/8738 "2023-12-22T17:26:08Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![DanielNovo](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/danielnovo/32/2155_2.png) [@DanielNovo](https://discourse.hibernate.org/u/DanielNovo)\
**Post date:** [December 22, 2023, 5:26pm UTC](https://discourse.hibernate.org/t/hibernate-6-criteria-hour-is-not-a-recognized-built-in-function-name/8738/1 "2023-12-22T17:26:08Z")

</div>

Hi,

I’m migrating from hibernate 5.3.24 to hibernate 6.2.13.

In a criteria query, i have a predicate

```auto
Predicate pred = cb.equal(cb. function("hour", Integer.class, root.get(MyTable_.startDateTime)),
				cb.function("hour", Integer.class, otherRoot.get(MyTable2_.startTime)));

```

With this version it throwing the following exception

Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select …)] [‘hour’ is not a recognized built-in function name.] [n/a]

com.microsoft.sqlserver.jdbc.SQLServerException: ‘hour’ is not a recognized built-in function name.

I notice that the generated query is not generating “datepart(hour, mytable\_.startDateTime)”

Same code in hibernate 5 works fine.

Do i miss something?

---

<div class="post-metadata">

**Author:** ![DanielNovo](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/danielnovo/32/2155_2.png) [@DanielNovo](https://discourse.hibernate.org/u/DanielNovo)\
**Post date:** [December 28, 2023, 4:41pm UTC](https://discourse.hibernate.org/t/hibernate-6-criteria-hour-is-not-a-recognized-built-in-function-name/8738/2 "2023-12-28T16:41:21Z")

</div>

Just tested with Hibernate ORM core version 6.4.1.Final and the issue remains.

---

<div class="post-metadata">

**Author:** ![Francisco\_MachadoUX](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/francisco_machadoux/32/2895_2.png) [@Francisco\_MachadoUX](https://discourse.hibernate.org/u/Francisco_MachadoUX)\
**Post date:** [December 29, 2023, 10:07am UTC](https://discourse.hibernate.org/t/hibernate-6-criteria-hour-is-not-a-recognized-built-in-function-name/8738/3 "2023-12-29T10:07:04Z")

</div>

I have found that in version 5.6 the function “hour” is being registered in the SQLServerDialect constructor.

```auto
public SQLServerDialect() {
		...
		registerFunction( "hour", new SQLFunctionTemplate( StandardBasicTypes.INTEGER, "datepart(hour, ?1)" ) );
		...
	}

```

But since version 6.0 this function is not registered in the constructor or in the initializeFunctionRegistry function.  
While in some other dialects, for example MySQLDialect, the function “hour” is registered using functionFactory.hourMinuteSecond() from the CommonFunctionFactory. This does not happen in the SQLServerDialect.

Could the “hour” function be added to the SQLServerDialect?

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [January 2, 2024, 11:19am UTC](https://discourse.hibernate.org/t/hibernate-6-criteria-hour-is-not-a-recognized-built-in-function-name/8738/4 "2024-01-02T11:19:39Z")

</div>

Hibernate 6 now supports extracting datetime fields natively through the [extract](https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#hql-function-extract) function, so you should use that. The `HibernateCriteriaBuilder` interface also exposes the convenience method `hour()` which you can use by simply passing it the datetime path expression and it should work like expected.

---

<div class="post-metadata">

**Author:** ![Francisco\_MachadoUX](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/francisco_machadoux/32/2895_2.png) [@Francisco\_MachadoUX](https://discourse.hibernate.org/u/Francisco_MachadoUX)\
**Post date:** [January 3, 2024, 10:00am UTC](https://discourse.hibernate.org/t/hibernate-6-criteria-hour-is-not-a-recognized-built-in-function-name/8738/5 "2024-01-03T10:00:31Z")

</div>

How could one use the extract function, `extract(year from datetime)`, with jpa criteria?

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [January 3, 2024, 10:12am UTC](https://discourse.hibernate.org/t/hibernate-6-criteria-hour-is-not-a-recognized-built-in-function-name/8738/6 "2024-01-03T10:12:15Z")

</div>

You can use the Hibernate Criteria extended APIs interface, like I mentioned in my previous reply. For example, to extract the year you could do something like:

```java
HibernateCriteriaBuilder cb = entityManager.unwrap( Session.class ).getCriteriaBuilder();
cb.year( root.get( "datetime_field" ) );

```

For more details see the User Guide chapter about [Crtiera extensions](https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#criteria-extensions).

---

<div class="post-metadata">

**Author:** ![Martin\_Schoepf](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/martin_schoepf/32/3401_2.png) [@Martin\_Schoepf](https://discourse.hibernate.org/u/Martin_Schoepf)\
**Post date:** [August 5, 2024, 2:41pm UTC](https://discourse.hibernate.org/t/hibernate-6-criteria-hour-is-not-a-recognized-built-in-function-name/8738/7 "2024-08-05T14:41:17Z")

</div>

Hello, just tried this example (upgrading missing ‘year’-function from hibernate 5 to 6):  
when doing  
_cb.year( root.get( “datetime\_field” ) );_  
I get  
_The method year(Expression\<? extends TemporalAccessor\>) in the type HibernateCriteriaBuilder is not applicable for the arguments (Path\<Date\>)_  
Any help appreciated!

---

<div class="post-metadata">

**Author:** ![DanielNovo](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/danielnovo/32/2155_2.png) [@DanielNovo](https://discourse.hibernate.org/u/DanielNovo)\
**Post date:** [August 5, 2024, 2:54pm UTC](https://discourse.hibernate.org/t/hibernate-6-criteria-hour-is-not-a-recognized-built-in-function-name/8738/8 "2024-08-05T14:54:43Z")

</div>

Hi,

Year function don’t support java.util.Date. Should use new java time api.

---

<div class="post-metadata">

**Author:** ![Martin\_Schoepf](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/martin_schoepf/32/3401_2.png) [@Martin\_Schoepf](https://discourse.hibernate.org/u/Martin_Schoepf)\
**Post date:** [August 5, 2024, 4:02pm UTC](https://discourse.hibernate.org/t/hibernate-6-criteria-hour-is-not-a-recognized-built-in-function-name/8738/9 "2024-08-05T16:02:58Z")

</div>

Thanks for your fast reply!  
Yes, will have to rework our legacy-model … 😉