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

[Christian Beikov]

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-sql?view=sql-server-2017
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev