Hibernate Treats SQL Server Dateparts as Columns Instead of Keywords

Question

We have an entity column with a formula property that requires today's date at midnight in order to calculate itself property:

DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)

Hibernate transforms this into:

DATEADD(mytablename0_.DD, DATEDIFF(mytablename0_.DD, 0, GETDATE()), 0)

I've double checked that my dialect is SqlServer. I've also replaced DD with DAY, but the problem remains.

How do I get hibernate to recognize that DD is a keyword?

Answer 1

I had the same issue when upgrading from Sybase to SQLServer. The field in my scenario was an integer field used to store a date since 1970-01-01. The DATEADD function was used to convert that integer into a genuine date so we had a good representation through the hibernate mapped objects.

My original mapping for this field was the following:

<property name="lostDate" type="date">
  <formula>dateadd(dd, lost_date, '01-JAN-1970')</formula>
</property>

This resulted in the problem that you described in the question, where the resulting sql passed to the database was:

dateadd( table0_.dd, table0_.lost_date, '01-JAN-1970')

Updating the mapping to surround the dateadd keyword dd in double quotes resolved this issue for me.

<property name="lostDate" type="date">
  <formula>dateadd("dd", lost_date, '01-JAN-1970')</formula>
</property>

SQL Server 2008 accepted the generated SQL below and executed as expected.

dateadd("dd", table0_.lost_date, '01-JAN-1970')

Answer 2

I had the same problem, you can actually solve the problem by replacing DD with "DD", but it won't work in some context.

Another technique that has solved everything in my case is to replace DD by DAY which is recognized by NHibernate