Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0010507Openbravo ERP01. General setuppublic2009-09-07 20:452009-09-13 19:31
Reporternetworkb 
Assigned Toiperdomo 
PriorityurgentSeveritymajorReproducibilitysometimes
StatusclosedResolutionno change required 
PlatformOS5OS Version
Product Version2.50MP1 
Target Version2.50MP6Fixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0010507: Date parse error when scheduling process
DescriptionWorking with US date format ('MM-DD-YYYY' in Openbravo.properties) when schedule a process an error occurs parsing date format. The system is trying to convert start date from format DD-MM-YYYY to MM-DD-YYYY.

It happens because the database is configured as european and "select date" queries obtain dates in european format, but the parser (in java) is configured as US format. When select method of src/org/openbravo/scheduling/trigger_data.xsql is executed the start date obtained is (DD-MM-YYYY).
Steps To Reproduce- Configure Openbravo Properties as mm-dd-yyyy
- Go to General Setup || Application || Process Scheduling || Process Scheduling
- Schedule one process
- Press button Schedule.
Proposed SolutionIn src/org/openbravo/scheduling/OBScheduler.java in line 347

final TriggerData data = TriggerData.select(conn, name); add new parameter in query : dateTimeFormat.

Add new parameter in xsql
In src/org/openbravo/scheduling/trigger_data.xsql modifye the method:

select .....TO_CHAR(STARTDATA,?)

where ? is dateTimeFormat.
Additional Information
TagsNo tags attached.
Relationships
duplicate of defect 0009202 closed iperdomo ProcessRunData has the date format hardcoded 
Attached Filespng schedule1.png (72,448) 2009-09-13 19:13
https://issues.openbravo.com/file_download.php?file_id=1747&type=bug
png

png schedule2.png (27,068) 2009-09-13 19:13
https://issues.openbravo.com/file_download.php?file_id=1748&type=bug
png
Issue History
Date ModifiedUsernameFieldChange
2009-09-07 20:45networkbNew Issue
2009-09-07 20:45networkbAssigned To => rafaroda
2009-09-07 20:45networkbOBNetwork customer => Yes
2009-09-08 07:17iperdomoAssigned Torafaroda => iperdomo
2009-09-08 07:19iperdomoRelationship addedduplicate of 0009202
2009-09-08 07:19iperdomoStatusnew => closed
2009-09-08 07:19iperdomoNote Added: 0019494
2009-09-08 07:19iperdomoDuplicate ID0 => 9202
2009-09-08 07:19iperdomoResolutionopen => duplicate
2009-09-09 00:00anonymoussf_bug_id0 => 2854709
2009-09-11 21:45networkbStatusclosed => new
2009-09-11 21:45networkbResolutionduplicate => open
2009-09-11 21:45networkbNote Added: 0019834
2009-09-11 21:47networkbTarget Version2.50MP1 => 2.50MP6
2009-09-12 06:42rafarodaPriorityimmediate => urgent
2009-09-12 06:42rafarodaStatusnew => scheduled
2009-09-13 19:13iperdomoFile Added: schedule1.png
2009-09-13 19:13iperdomoFile Added: schedule2.png
2009-09-13 19:30iperdomoNote Added: 0019865
2009-09-13 19:31iperdomoStatusscheduled => closed
2009-09-13 19:31iperdomoDuplicate ID9202 => 0
2009-09-13 19:31iperdomoResolutionopen => no change required

Notes
(0019494)
iperdomo   
2009-09-08 07:19   
I tested using US date format, dd-mm-yyyy and I was able to schedule a process. This is fixed since 2.50MP2.

Cheers,

Iván
(0019834)
networkb   
2009-09-11 21:45   
This is not the same issue than 0009202. The bug fix of 0009202 was applied and kept giving the error.

When we applied the proposed solution, the error was solved. So please, can you reconsider this issue?

Thank you.
(0019865)
iperdomo   
2009-09-13 19:30   
Hi,

Probably true that is not the same problem, but I'm able to schedule a process using US date format (mm-dd-yyy). Attached you'll find the process request (schedule1.png) and the process execution (schedule2.png).

If you want to use US date format, you must change the Openbravo.properties:
####################
# Date/time format #
####################

dateFormat.js=%m-%d-%Y
dateFormat.sql=MM-DD-YYYY
dateFormat.java=MM-dd-yyyy
dateTimeFormat.java=MM-dd-yyyy HH:mm:ss

But also the bbdd.sessionConfig:

When using PostgreSQL:
bbdd.sessionConfig=select update_dateFormat('MM-DD-YYYY')

When using Oracle:
bbdd.sessionConfig=ALTER SESSION SET NLS_DATE_FORMAT='MM-DD-YYYY' NLS_NUMERIC_CHARACTERS='.,'

After changing the Openbravo.properties, and assuming that you have the application compiled, you *must* execute ant compile.complete. This step is required because SqlC uses the dateFormat.java dateTimeFormat.java properties when generating the Data class.

In the TriggerData case, you have to lines like this ones:
objectTriggerData.startDate = UtilSql.getDateValue(result, "start_date", "MM-dd-yyyy");
objectTriggerData.finishesDate = UtilSql.getDateValue(result, "finishes_date", "MM-dd-yyyy");

The part "MM-dd-yyyy" is using the dateFormat.java property.

There is no need to pass the dateTime to trigger_data.xsql, what happened is that because you changed the _data.xsql when you compiled this class got regenerated and therefore the new dateFormat was used.

The to_char() call is extracting the hours:minutes:seconds and there is no need to pass the datetime format. e.g.

SELECT TO_CHAR(Start_Time, 'HH24:MI:SS') AS Start_Time, Start_Date, TO_CHAR(Finishes_Time, 'HH24:MI:SS') AS Finishes_Time, Finishes_Date FROM AD_Process_Request ORDER BY Created DESC LIMIT 1;

 start_time | start_date | finishes_time | finishes_date
------------+---------------------+---------------+---------------
 19:08:00 | 2009-09-13 00:00:00 | |


So the start_time is calculated correctly.