[OFBIZ-1001] Oracle Database Date Query Problems

• Type: Wish
• Status: Closed
• Priority: Minor
• Resolution: Later
• Component/s: framework

Environment

SUSE Linux 10 & Oracle DB

• Assignee: David E. Jones
• Reporter: Michael Imhof
• Created: 21/May/07 06:26
• Updated: 21/Apr/14 12:51
• Resolved: 21/Apr/14 12:51

Description

Problem with the date conversion.
The Following SQL Statement is not working on a Oracle Database
SELECT .... WHERE RUN_TIME <= '2007-05-15 07:45:10.875' AND ....
Using an Oracle database, this statement is wrong and generates the ORA-01861 error.
The statement should look like this:
SELECT .... WHERE RUN_TIME <= TO_DATE('2007-05-15 07:45:10', 'YYYY-MM-DD HH:MI:SS') AND ....

Solutions
=======
1. Use Oracle Trigger: http://docs.ofbiz.org/display/~jacopoc/OFBiz+and+Oracle
2. Set Oracle Date Format: ALTER SYSTEM set NLS_TIMESTAMP_FORMAT='RRRR-MM-DD HH24:MI:SS.FF'
3. This patch.

The patch is a general solutions based on the design hibernate is using. (Every driver has a Dialect class).
All databases are working like before because the default implementation is just returning the input string (only
for oracle databases, a special string is returned).