Filter by Date Field for Date type appears broken for Locale English (United Kingdom) - Forum - Rollbase - Progress Community

Filter by Date Field for Date type appears broken for Locale English (United Kingdom)

 Forum

Filter by Date Field for Date type appears broken for Locale English (United Kingdom)

  • Hello,

    My Task Object has Attribute Task and receives a Due Date of Type Date.

    In my Search Component I enable the Filter by Date Field but no records are returned when I use 'Last 30 Days' for example despite records existing that meet the criteria.

    Logging the SQL to query.log appears to show the problem...

    SELECT COUNT(A.OBJ_ID) FROM RB_OBJ_DATA A WHERE A.OBJ_DEF_ID=5264943 AND A.EVENT_DATE>=STR_TO_DATE('27.05.2017', '%m/%d/%Y') AND A.EVENT_DATE<STR_TO_DATE('26.06.2017', '%m/%d/%Y')

    The format looks to have the wrong separators and locale.

    Rewriting the query format strings and manually running from System:

    SELECT COUNT(A.OBJ_ID) FROM RB_OBJ_DATA A WHERE A.OBJ_DEF_ID=5264943 AND A.EVENT_DATE>=STR_TO_DATE('27.05.2017', '%d.%m.%Y') AND A.EVENT_DATE<STR_TO_DATE('26.06.2017', '%d.%m.%Y')

    Query Results
    Column Settings
    COUNT(A.OBJ_ID)
    8

    Additionally using Created At causes the following Query to be emitted (although this returns records it doesn't achieve the required functionality)...

    SELECT COUNT(A.OBJ_ID) FROM RB_OBJ_DATA A WHERE A.OBJ_DEF_ID=5264943 AND A.CREATED_AT>=STR_TO_DATE('05/26/2017 23:00:00', '%m/%d/%Y %H:%i:%s') AND A.CREATED_AT<STR_TO_DATE('06/25/2017 23:00:00', '%m/%d/%Y %H:%i:%s')

    Regards

    --

    Charles.

  • We still have this issue whereby searching on a Date field causes Rollbase to create a query string with the incorrect format separators and prevents any search data being returned, alternative query.log example:

    SELECT COUNT(A.OBJ_ID) FROM RB_OBJ_DATA A WHERE A.OBJ_DEF_ID=5707140 AND A.DATE1>=STR_TO_DATE('01.01.2018', '%m/%d/%Y') AND A.DATE1<STR_TO_DATE('01.01.2019', '%m/%d/%Y')

    Regards

    --

    Charles

  • Hi,

    Can you share the DB type  on which you are able to reproduce this issue ?

    It will be helpful in investigating this scenario.

    Thanks

    Jaya

  • MySQL 5.6.27 on Linux

  • This appears due to the use of:

    DefaultDateFormat=2