[hibernate-dev] How to deal with a constant in CriteriaQuery

[Gail Badner]

SQL Server defines a function:

DATEDIFF ( datepart , startdate , enddate )  [1]

(This method still needs to be added to SQLServer2012Dialect.)

datepart can be one of a variety of time values, e.g., day, week, year, etc.

On SQL Server (at least) the value for datepart cannot be treated as a
literal (enclosed in quotes) or bound as a parameter.

This causes problems when using DATEDIFF in a CriteriaQuery.

final Expression<Integer> diff = cb.function("DATEDIFF", Integer.class,
cb.literal("day"), ... ).as(Integer.class);

SQL Server throws:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid parameter 1
specified for datediff.

There is no problem using the function in a query, as long as datepart is
not enclosed in quotes or bound to the query:

"select datediff( day, ... ) from ..."

Is there some way to set a literal value using a CriteriaQuery that will
not ultimately be enclosed in quotes or bound to a query?

I've also been trying to find an integer constant that would be equivalent
to 'day', but haven't had any luck.

Anyone have an idea how this could be done with CriteriaQuery?

Thanks,
Gail

[1]
https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017