I was going to suggest the PreparedStatement solution as well, but if you MUST do it the hard way.. the oracle cast for a date uses a specific format.  For example, it'd be cast('01-Jan-2005' as date)... and while I don't have derby handy... :-P but I'm not sure that format would work in Derby.  In the end, you're doing yourself a HUGE favor by switching to PreparedStatement.  The big fuss against the PS is that it's not easy to log the params for debugging.  I suggest you look at this for help there.  http://www.javaworld.com/javaworld/jw-01-2002/jw-0125-overpower.html?page=1

You could change their factory to only use the loggable PS when your logger is in debug mode or whatever.

Hope this helps!
Brad

On Wed, Feb 4, 2009 at 1:34 PM, Stephan van Loendersloot (LIST) <stephan@republika.nl> wrote:

Kathey Marsden wrote:
Kent Spaulding wrote:

Is there some query format that will for both?
I don't have access to Oracle, but would it work to use an explicit cast
and cast(d.insertion_date as date) <= CAST('2009-02-28'  AS DATE);


I don't have access to Oracle either, so my preffered solution would be (again) to use a PreparedStatement

Example code (not very efficient, the DateFormat should be reused instead of recreating it each time. Preferrably by using it from a ThreadLocal since it's not thread-safe):

public static void getSqlDate(String date) {
  java.sql.Date result = null;
  DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
  try {
     result = new java.sql.Date(dateFormat.parse(date).getTime());
  } catch (ParseException e) {
     // Logging goes here
  }
  return result;
}


String strDate = "2009-02-28";

PreparedStatement pstmt = conn.prepareStatement("SELECT d.insertion_date FROM d WHERE CAST(d.insertion_date as date) >= ?");
pstmt.setDate(1, getSqlDate(strDate));


Regards,

  Stephan.