db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kent Spaulding <k...@iotabits.com>
Subject Re: Derby/Oracle - single SQL query?
Date Wed, 04 Feb 2009 18:49:38 GMT
Thanks, I should have explained that these are textual queries given  
as config string in a reporting tool, so I can't use PreparedStatements.

I think I can replace the Oracle NLS_DATE_FORMAT in a logon trigger,  
so I'll try that, after attempting the cast that Kathey suggested.

I'd like to avoid the trigger if possible... but just for reference,  
here's what I expect it'll look like:

CREATE OR REPLACE TRIGGER LOGINTRG
AFTER LOGON ON DATABASE
BEGIN
if (user = 'myuser') then
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD''';
end if;
END LOGINTRG;


On Feb 4, 2009, at 10:34 AM, Stephan van Loendersloot (LIST) 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.
>
>
>


Mime
View raw message