Some date patterns (may) lead to print problems


The date pattern field in the language window is used for formatting dates when printing.
A java style date pattern is expected. Some of these patterns are leading to errors when the database used is Oracle:
when you use "MMM" ( the java pattern for the abbreviated month name) or "MMMM" (for the full month name) for the customers language and try to print an invoice you get error "ORA-01821: date format not recognized" instead.
Reason is that DataEngine.getPrintData is constructing SQL to combine document number, date and amount using this pattern which is different from the SQL date pattern for abbreviated or full month name.
The SQL part in question is

(SELECT NVL(C_Invoice.DocumentNo,'-1') ||''|| NVL(TRIM(TO_CHAR(C_Invoice.DateInvoiced,'MMM/DD/YYYY')),'-1') ||''|| NVL(TRIM(TO_CHAR(C_Invoice.GrandTotal,'999G999G999G990D00')),'-1')

It is constructed by DB_Oracle.TO_CHAR called from MLookupFactory.getLookup_TableDirEmbed called from DataEngine.getPrintDataInfo.

Postgresql does not throw an error but interprets "MMM" as "MM" followed by an "M" and "MMMM" as two times "MM", leading to "02M" or "0202" for a date in February,

The correct pattern for SQL would be "MON" for abbreviated name or "MONTH" for full name.
Solution is a pattern translation in DB_Oracle.TO_CHAR and DB_Postgresql.TO_CHAR before returning the constructed string.




Dirk Niemeyer
February 11, 2019, 11:28 AM

Two brute force looking patches attached. Using pattern matching may look better but might be worse performance wise .

Carlos Ruiz
February 15, 2019, 5:26 PM

Thanks .

I committed a different patch converting the month back to numeric.

I think a correct solution would be to implement a column AD_Language.DBDatePattern, but even there still we need to cope with the language translation of the month when the login language doesn't match with the database language.


Carlos Ruiz


Carlos Ruiz


Dirk Niemeyer


Tested By



Fix versions