title: Java EclipseLink Persistence JPQL Operator: TruncateDate
body: <p>I have seen several answers and documentation on how to use operators, but cannot get this to work. I want to know where I can find use case on TruncateDate OPERATOR. I have looked at the following and they are not helpful:</p>
  
  <p><a href="http://www.eclipse.org/eclipselink/api/2.5/org/eclipse/persistence/expressions/Expression.html#truncateDate(java.lang.String)" rel="nofollow noreferrer">http://www.eclipse.org/eclipselink/api/2.5/org/eclipse/persistence/expressions/Expression.html#truncateDate(java.lang.String)</a></p>
  
  <p><a href="https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Functions" rel="nofollow noreferrer">https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Functions</a></p>
  
  <p>What I need is just a simple query that works with the TruncateDate operator. Here is my query:</p>
  
  <pre><code>@NamedQueries({
  @NamedQuery(name="SprMessage.findPendingMessageIds", 
              query="SELECT s.messageId "
                    + "FROM SprMessage s "
                   + "WHERE s.inOrOut = 'INBOUND'"
                     + "AND s.messageStatus = 'RECEIVED'"
                      + "OR (s.messageStatus = 'RETRY' AND s.lastUpdateDate &lt; OPERATOR('TruncateDate', :todayIn)) "
                + "ORDER BY s.createdDate ASC")
  })
  </code></pre>
  
  <p>If I replace OPERATOR('TruncateDate', :todayIn) with :todayIn it works. Is this even possible with a parameter? My issue is I do not want to check seconds and minutes, etc. Just down to the day.</p>
  
  <p>Thank you in advance for any help on the matter.</p>
  
  <p>Additional information on my JPA version:</p>
  
  <pre><code>compile (group: 'org.eclipse.persistence', name: 'eclipselink', version: '2.5.1')
  compile (group: 'org.eclipse.persistence', name: 'javax.persistence', version: '2.1.0', ext: 'jar') 
  </code></pre>
  
  <p>I could do this in code with JODA time but I would really like to see this work in JPA for the sake of IT.</p>
  
  <p>Due to a comment about database system, this is being run against an Oracle 11g database, as well as a Derby database for the JUnits which are failing.</p>
  
  <p>Here is the error I am getting running:</p>
  
  <pre><code>Local Exception Stack: 
  Exception [EclipseLink-6047] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.QueryException
  Exception Description: Invalid operator [platform operator - TruncateDate] in expression.
  Query: ReportQuery(name="SprMessage.findPendingMessageIds" referenceClass=SprMessage jpql="SELECT s.messageId FROM SprMessage s WHERE s.inOrOut = 'INBOUND'AND s.messageStatus = 'RECEIVED'OR (s.messageStatus = 'RETRY' AND s.lastUpdateDate &lt; OPERATOR('TruncateDate', :todayIn, 'dd')) ORDER BY s.createdDate ASC")
      at org.eclipse.persistence.exceptions.QueryException.invalidOperator(QueryException.java:664)
      at org.eclipse.persistence.internal.expressions.FunctionExpression.initializePlatformOperator(FunctionExpression.java:330)
      at org.eclipse.persistence.internal.expressions.FunctionExpression.getPlatformOperator(FunctionExpression.java:307)
      at org.eclipse.persistence.internal.expressions.FunctionExpression.printSQL(FunctionExpression.java:544)
      at org.eclipse.persistence.expressions.ExpressionOperator.printDuo(ExpressionOperator.java:2239)
      at org.eclipse.persistence.internal.expressions.CompoundExpression.printSQL(CompoundExpression.java:286)
      at org.eclipse.persistence.internal.expressions.RelationExpression.printSQL(RelationExpression.java:872)
      at org.eclipse.persistence.expressions.ExpressionOperator.printDuo(ExpressionOperator.java:2239)
      at org.eclipse.persistence.internal.expressions.CompoundExpression.printSQL(CompoundExpression.java:286)
      at org.eclipse.persistence.expressions.ExpressionOperator.printDuo(ExpressionOperator.java:2239)
      at org.eclipse.persistence.internal.expressions.CompoundExpression.printSQL(CompoundExpression.java:286)
      at org.eclipse.persistence.internal.expressions.ExpressionSQLPrinter.translateExpression(ExpressionSQLPrinter.java:306)
      at org.eclipse.persistence.internal.expressions.ExpressionSQLPrinter.printExpression(ExpressionSQLPrinter.java:129)
      at org.eclipse.persistence.internal.expressions.SQLSelectStatement.printSQL(SQLSelectStatement.java:1683)
      at org.eclipse.persistence.platform.database.DerbyPlatform.printSQLSelectStatement(DerbyPlatform.java:373)
      at org.eclipse.persistence.internal.expressions.SQLSelectStatement.buildCall(SQLSelectStatement.java:782)
      at org.eclipse.persistence.internal.expressions.SQLSelectStatement.buildCall(SQLSelectStatement.java:792)
      at org.eclipse.persistence.descriptors.ClassDescriptor.buildCallFromStatement(ClassDescriptor.java:813)
      at org.eclipse.persistence.internal.queries.StatementQueryMechanism.setCallFromStatement(StatementQueryMechanism.java:390)
      at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.prepareReportQuerySelectAllRows(ExpressionQueryMechanism.java:1696)
      at org.eclipse.persistence.queries.ReportQuery.prepareSelectAllRows(ReportQuery.java:1203)
      at org.eclipse.persistence.queries.ReadAllQuery.prepare(ReadAllQuery.java:744)
      at org.eclipse.persistence.queries.ReportQuery.prepare(ReportQuery.java:1071)
      at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:661)
      at org.eclipse.persistence.queries.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:901)
      at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:613)
      at org.eclipse.persistence.internal.jpa.QueryImpl.getDatabaseQueryInternal(QueryImpl.java:341)
      at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createNamedQuery(EntityManagerImpl.java:1124)
      at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createNamedQuery(EntityManagerImpl.java:1144)
      at com.paychex.corp.app2app.iatosfdc.WarTransactionsHandler.getPendingMessageIds(WarTransactionsHandler.java:102)
      at com.paychex.corp.app2app.iatosfdc.WarTransactionsHandlerTest.testGetPendingMessageBatchGetsMessagesById(WarTransactionsHandlerTest.java:102)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
      at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
      at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
      at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
      at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
      at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:30)
      at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
      at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
      at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
      at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
      at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
      at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
      at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
      at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
      at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
      at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
      at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
      at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
  </code></pre>