[Bug]: Formula doesn't handle dates before 1900-01-01

[Fjelley85]

Apache Hop version?

2.7.0

Java version?

11.0.19

Operating system

Windows

What happened?

In formula: Date(1800,1,1) results in date = 3700-01-01

See screenshot 1

In IF's, the result for doing a date before 1900-01-01 yields if the date below 1900-01-01 is not produced in the formula box. For example, IF(1=2, DATE(2000,1,1), [dates]) where [dates] = 1800-01-01. See screenshot 1

reproduce by using generate rows and formula

Issue Priority

Priority: 2

Issue Component

Component: Transforms

[bamaer]

the Formula transform uses Apache POI for Excel-like formulas.

quite crazy, but Microsoft Excel doesn't seem to support dates before 1900-01-01 in formulas: https://learn.microsoft.com/en-US/office/troubleshoot/excel/calculate-age-before-1-1-1900

[Fjelley85]

That's crazy, seeing the reason I've notice this is because Navision (Microsoft dynamics) set their "null" dates to around 1750s in stead of null. I guess this won't be fixed then which means I need to go through every pipeline for our Navision integration after migrating from Pentaho?

[bamaer]

Not sure if it won't be fixed, but it definitely is not a trivial one-line bug fix. This would require us to check for this specific case and add a fix that doesn't cause any side unwanted side effects. For context: the Formula transform in Apache Hop is not a port of the PDI transform (which uses an LGPL licensed dependency). This transform is a complete rewrite that is largely compatible, but the more or less minor incompatibilities can have a significant impact.

[Fjelley85]

Is there a timeline when this fix will be implemented?

[hansva]

As a project, we do not provide timelines for individual tickets. It depends on when one of the volunteers is interested and has time to do the work. There are parties that provide commercial support or development. You can find a list of these parties here