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: pass (nike.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Date: Wed, 04 Feb 2009 10:43:54 -0800
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Derby/Oracle - single SQL query?
In-reply-to: <032D6760-74DE-4AB7-B033-11E16CCB9473@iotabits.com>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <4989E1EA.3090508@sun.com>
References: <032D6760-74DE-4AB7-B033-11E16CCB9473@iotabits.com>
User-Agent: Thunderbird 2.0.0.19 (Macintosh/20081209)

Kent Spaulding wrote:
> Hi again,
>
> Thanks to the list, I'm able to use the same code for inserts for both 
> Oracle and Derby drivers.
>
> Now I have a SQL question, one of my queries has where clauses 
> (actually, all of them)..
>
> This works in Derby, not Oracle:
>
>    and cast(d.insertion_date as date) >= '2009-01-01'
>    and cast(d.insertion_date as date) <= '2009-02-28'
>
> Derby is smart enough to treat the literal as a DATE.
>
> This works in Oracle, not Derby:
>
>    and cast(d.insertion_date as date) >= to_date('2009-01-01', 
> 'YYYY-MM-DD')
>    and cast(d.insertion_date as date) <= to_date('2009-02-28', 
> 'YYYY-MM-DD')
>
> Have to use a function to convert the literal, AFAIK.
>
> Is there some query format that will for both?
> If no, can I add a to_date function to Derby?
Hi Kent,

You can add your own user-defined to_date function, but you can't add a 
system function. The big difference, from the point of your syntax, is 
that a user defined function has to live in the current schema. In 
contrast, a system function is global across the entire database. 
Details on how to create a user-defined function can be found in the 
Reference Guide: 
http://db.apache.org/derby/docs/dev/ref/ref-single.html#crefsqlj95081

Hope this helps,
-Rick
>
> Thanks in advance,
> --Kent