What format does the Date() function expect?

SugarCRM uses the format

2015-12-31 10:00:00 format by default.

It automatically converts according to user preferences.

When you do any kind of coding then you have to use as given above.

It looks like you are just creating another date from your existing last_inspection_date_c field. What is it you are trying to do?

Thanks for your interest in my problem.

The design is not of my doing, I've taken over the client after the last provider ran away, but I'm confident that I understand his intentions.

The value being rear is an integer, e.g. 20151211, which is used to store the rollup of a similar integer field within a child module (inspection reports).

That value is then to be converted into a datetime field (I'm not sure why he opted for datetime instead of simply date - annoying IMO) using the field's calculation given above so that it can be used as a date value for some other processes.

It works if the user has the date format preference of 23/12/2010.  If they have the format of 23-12-2010 then I seem to have to change the contents of the calculated field to match this - seems odd to me.  No matter what the user's date preference, if a Mass Update is performed then the system will only work if the calculated field formula is set as below...

date(toString(concat(subStr(toString($last_inspection_date_calc_c),0,4),"-",subStr(toString($last_inspection_date_calc_c),4,2),"-",subStr(toString($last_inspection_date_calc_c),6,2))))

So the net result of all this is that if I want to get it to work I need to either;

• detect what date configuration is in play for any given save (is that even possible?) and use a different formula for each format.
• ensure that all users have the date format preference which matches that which sugar uses during bulk update processes (2010-12-23).

They're using using Version 6.5.16 (Build 1082).

Thanks for your input, but I've done some further testing and I'm observing contradictory behaviour when testing the software's behaviour.

The date() functions behaviour is demonstrating that it expects a format that matches the user's date format preference.  During Mass Updates, it is demonstrating that it expects the format 2015-12-31.

So if I want it to work, I need to ensure that each users date preference matches what Sugare requires during Mass Updates, and have a matching formula within this field

i.e. date(toString(concat(subStr(toString($last_inspection_date_calc_c),0,4),"-",subStr(toString($last_inspection_date_calc_c),4,2),"-",subStr(toString($last_inspection_date_calc_c),6,2))))

In this case you may want to use a logic hook to achieve what you want. If you decide to do that save the date in mysql datetime format but remember you want to same UTC datetime in the database.

Not an answer but perhaps a less stressful solution.

Thanks Shad, with my further testing it seems to me that this approach, like many others within this installation of SugarCRM (my first, and what I'm trying to learn from !!!!) is fighting with the approach/s intended by the Sugar developers.  So what you are saying rings true for me.

I'll attempt to educate myself on logic hooks and rethink the solution once I am armed with that knowledge.

I've simply madated to users that they set their date format to match what teh Bulk Update uses - that is the first date foramt option: 2010-12-23.

I've set the calculated field to match this format and all is finally well.  The client is satisfied with this.

Thanks for the assistance, hopefully I can return the favour one day.