Hey Gail,
I usually register a dedicated SQLFunction for every time value in the
Dialect and use these functions instead. So in your case, I'd have a
"day" function and use it like
final Expression<Integer> diff = cb.function("DAY", Integer.class, ...
).as(Integer.class);
The fuction will then render the SQL like it is supposed to be
"select datediff( day, ... ) from ..."
Although I'm not 100% sure that CriteriaBuilder.function properly
resolves the function since I never used custom function with the JPA
Criteria API directly, I'd at least expect it.
Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 24.04.2018 um 07:29 schrieb 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-sq...
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev