Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (nike.apache.org: 83.137.146.145 is neither permitted
 nor denied by domain of stephan@republika.nl)
Message-ID: <529211275.62081233772472744.JavaMail.james@republika.nl>
MIME-Version: 1.0
Date: Wed, 04 Feb 2009 19:34:32 +0100
From: "Stephan van Loendersloot (LIST)" <stephan@republika.nl>
Organization: REPUBLiKA B.V.
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Derby/Oracle - single SQL query?
References: <032D6760-74DE-4AB7-B033-11E16CCB9473@iotabits.com>
 <4989DB49.6000702@sbcglobal.net>
In-Reply-To: <4989DB49.6000702@sbcglobal.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


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.