Eclipselink mishandles EXTRACT(DATE FROM X) clause in DB2 and SQLServer

[KyleAure]

Current behavior

When attempting to run a query against a persisted entity table using the EXTRACT(DATE FROM X) clause, the resulting SQL generated by EclipseLink is not supported by DB2 and SQLServer databases.

For example, using the following entity:

@Entity
public class Showtime {

    @Id
    @GeneratedValue
    public Integer id;

    public String movie;
    public LocalDateTime startTime;
    public LocalDateTime endTime;

    public Showtime() {
    }
...
}

When executing the query method:

Stream<Showtime> showtimesToday = em.createQuery("SELECT NEW io.openliberty.jpa.data.tests.models.Showtime(id, movie, startTime, endTime) "
                                            + "FROM Showtime "
                                            + "WHERE EXTRACT(DATE FROM startTime) = ?1 "
                                            + "ORDER BY movie DESC", Showtime.class)
                            .setParameter(1, LocalDate.now())
                            .getResultStream();

The resulting SQL and exception is generated for DB2:

SELECT ID, MOVIE, STARTTIME, ENDTIME FROM SHOWTIME
WHERE (EXTRACT(DATE FROM STARTTIME) = ?)
ORDER BY MOVIE DESC 

jakarta.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 5.0.0-B10.v202508250733-7403fb4d27505a202052781d38a7d5324ff916bd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=DATE;TIME WHERE (EXTRACT(;<extract_datetime_keyword>, DRIVER=4.34.30
Error Code: -104
Call: SELECT ID, MOVIE, STARTTIME, ENDTIME FROM SHOWTIME WHERE (EXTRACT(DATE FROM STARTTIME) = ?) ORDER BY MOVIE DESC
bind => [2025-09-16]
Query: ReportQuery(referenceClass=Showtime sql="SELECT ID, MOVIE, STARTTIME, ENDTIME FROM SHOWTIME WHERE (EXTRACT(DATE FROM STARTTIME) = ?) ORDER BY MOVIE DESC")
at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:389)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:265)
at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:480)
at jakarta.persistence.TypedQuery.getResultStream(TypedQuery.java:93)
at io.openliberty.jpa.data.tests.web.JakartaDataRecreateServlet.testOLGHXXXXX(JakartaDataRecreateServlet.java:2506) 

The resulting SQL and exception is generated for SQLServer:

SELECT ID, MOVIE, STARTTIME, ENDTIME FROM SHOWTIME FROM SHOWTIME
WHERE (DATEPART(DATE,STARTTIME) = ?)
ORDER BY AMOUNT DESC

jakarta.data.exceptions.DataException: jakarta.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 5.0.0-B10.v202508250733-7403fb4d27505a202052781d38a7d5324ff916bd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: 'DATE' is not a recognized datepart option.
Error Code: 155
Call: SELECT ID, MOVIE, STARTTIME, ENDTIME FROM SHOWTIME FROM SHOWTIME WHERE (DATEPART(DATE,STARTTIME) = ?) ORDER BY AMOUNT DESC
	bind => [1 parameter bound]
Query: ReportQuery(referenceClass=RebateEntity sql="SELECT ID, MOVIE, STARTTIME, ENDTIME FROM SHOWTIME FROM SHOWTIME WHERE (DATEPART(DATE,STARTTIME) = ?) ORDER BY AMOUNT DESC")

Expected Behavior

I would expect the SQL generated for DB2 to be like:

SELECT ID, MOVIE, STARTTIME, ENDTIME FROM SHOWTIME
WHERE (CAST(STARTTIME AS DATE) = ?)
ORDER BY MOVIE DESC 

I would expect the SQL generated for DB2 to be like:

SELECT ID, MOVIE, STARTTIME, ENDTIME FROM SHOWTIME
WHERE (CONVERT(DATE,STARTTIME) = ?)
ORDER BY MOVIE DESC

[pardhiv-krishna]

EclipseLink issue: 2526