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) <
[email protected]> 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.
>
>
>
>
