db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Derby/Oracle - single SQL query?
Date Wed, 04 Feb 2009 18:43:54 GMT
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


Mime
View raw message