db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Derby/Oracle - single SQL query?
Date Wed, 04 Feb 2009 20:10:22 GMT
>>>>>>>>>>>> Kent Spaulding wrote (2009-02-04 10:05:30):
> 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?

Try 
    and cast(d.insertion_date as date) >= {d '2009-01-01'}
    and cast(d.insertion_date as date) <= {d '2009-02-28'}

> If no, can I add a to_date function to Derby?
>
> Thanks in advance,
> --Kent

Mime
View raw message