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

[Gail Badner]

Yes, that should work with CriteriaQuery as well. It's a reasonable
workaround.

If JPA doesn't support this now, is it something that should be supported
in the future?

On Mon, Apr 23, 2018 at 11:45 PM, Christian Beikov <
christian.beikov at gmail.com> wrote:

> 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
>
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev