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.
