Workflow - Some Date calculations fail with certain formats

[SinergiaCRM]

When having a workflow action that runs on schedulers and calculates a date field using the date formulas provided https://docs.suitecrm.com/user/advanced-modules/workflow-calculated-fields/ , the results are wrong with certain combination of user date formats and values.

This can be reproduced if the user has the date format dd/mm/YYYY, and the day value is higher than 12, for example 15/12/2022. In this case, when applying the function {datediff({P0}; {now(Y-m-d)} ; months )} the DateTime function can't convert properly the "15/12/2022" date and it returns error.

The issue is also replicable in "On save" Workflows if the date is hard-coded in the formula.

Issue

Date field calculation gives error with certain formats and values

Expected Behavior

It should work with any format and values.

Actual Behavior

The workflow fails and the date isn't calculated.

Possible Fix

Detect user format and convert to DB/UTC format in those cases that are needed.

Steps to Reproduce

1. Set the User date format in Profile Advance option to "23/12/2010"
2. Create a workflow with a Calculate field action (Contacts based)
3. Add a date field parameter as parameter (birthdate)
4. Add a formula to a text field. (Formula: {datediff({P0}; {now(Y-m-d)} ; months )} to description field)
5. Create a contact with 14/12/2020 in birthdate
6. Run the workflow on Scheduler and check that it failed calculating the date

Context

This should work as expected

Your Environment

• SuiteCRM Version used: 7.12.8
• Browser name and version: Versión 107.0.5304.121 (Build oficial) (64 bits)
• Environment name and version: MariaDB 10.6.5, PHP 7.4
• Operating System and version : KDE Neon 5.26