What format does the Date() function expect?

 

I'm seeing the below error in my client's log file.


01/05/16 18:46:01 [1936][1][FATAL] Exception evaluating expression in SetValueAction, date(toString(concat(subStr(toString($last_inspection_date_calc_c),6,2),"/",subStr(toString($last_inspection_date_calc_c),4,2),"/",subStr(toString($last_inspection_date_calc_c),0,4)))) : attempt to convert invalid value to date: 11/12/2015

I'm not 100% sure, but it seems to be only occuring for some users.  Does the function perhaps expect me to provide the date in a format that matches each users date format preferences?

The formula is within a calculated field.

Thanks in advance

Parents
  • 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).

Reply
  • 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).

Children