using the findWhere() function

[tom_seminckx]

Hi All,

I am trying to get a list of all master agreements that have been created today. Therefore, i am using the following code:

Calendar currentDate = Calendar.getInstance();
SimpleDateFormat formatter= new SimpleDateFormat("MMM d, yyyy");
String dateNow = formatter.format(currentDate.getTime());

contractHome = IBeanHomeLocator.lookup(session,com.frictionless.api.contracts.ContractIBeanHomeIfc.sHOME_NAME);
contractList = contractHome.findWhere("BASE_DATE_DATE = " + currentDate.toString());

Unfortunately, this code is always giving me the following error:

SQL command not properly ended.

So my question, how can I use the findwhere function, and is the date format correct?

regards,

Tom

[former_member187934 · Accepted solution]

Tom,

This is more of an Oracle question but these suggestions might help.

3 things...

1. Try changing the format on your SimpleDateFormat to "MM-DD-YYYY"

2. I see you are not using the dateNow string in the findWhere() call.

3. use the Oracle TO_DATE() function in findWhere() : "BASE_DATE_DATE = TO_DATE('"\+dateNow\+"', 'MM_DD_YYYY')"

regards,

Alan

Edited by: Alan Salgado on Mar 8, 2011 9:20 AM

Edited by: Alan Salgado on Mar 8, 2011 9:22 AM

[tom_seminckx]

Hi Alan,

I'm afraid that this cannot work, as to_string() is not an oracle function i believe.

regards,

Tom

[former_member187934]

You are absolutely right Tom,

that function is supposed to be TO_DATE not TO_STRING

[tom_seminckx]

Hi Alan,

Indeed, I had to create the 'dateNow' string. But still I am getting the same error. Do you maybe have an idea how I can see what exact date format to use?

Thanks,

Tom

[former_member187934]

Tom,

I had to make a couple of edits to my post since I left out some formatting in case you copied and pasted.

If you copied from an older version, here's what the 'end' sring should look like for the findWhere():

Please note the use of 'single quotes' in the string passed to findWhere()

findWhere("BASE_DATE_DATE = TO_DATE('03-08-2011', 'MM-DD-YYYY')")

Any date format should work as long as you are telling TO_DATE what that format is in the second parameter.

Alan