Really hate dates in Oracle but all I want to do is just to a basic query to find whether a row or rows that are equal to a date (the format of the date is 17-JAN-11 in the Oracle table). I am using Toplink to try and form the query.
My code is like this:
ReadAllQuery query = new ReadAllQuery(FooMyTable.class);
ExpressionBuilder builder = query.getExpressionBuilder();
if (dateFoo != null)
query.setSelectionCriteria(builder.getField("REQUEST_CREATED_DATE").equal(dateFoo));
....
It always brings back zero rows and I guess its because I should use truncateDate() or something but I can't figure out the syntax. Can anyone help??, couldn't find anything decent via Google hence this posting.
UPDATE: Now I know it's something definitely to do with time and dates - for rows without time, just a date on it's own, the code above works, but if the date value also has a time with it then it fails.
Actual code needed is:
query.setSelectionCriteria(builder.getField("REQUEST_CREATED_DATE").truncateDate("DD").equal(dateFoo));
The error message:
ORA-01898: too many precision specifiers Error Code: 1898
Was due to the format mask being incorrect - as I required to have the date rounded up to the neareset day.