Summary:

Using relative values on date-only fields on Advanced Find screens may result in incorrect information being displayed if CRM users are in a different time zone to the CRM server. The issue is due to a relative value (+- time zone offset) being applied to SQL queries used in the Find.

Symptoms:

The issue may be seen when attempting to do an Advanced Find on a date-only field where a user is in a different time zone to the CRM server.

As an example, consider a user based on the east coast of the US (Eastern Time, UTC -5), where the CRM server is in Dublin (UTC). The user may attempt to do a search on a date of birth field on a Person record, which would be a date-only field. If they were to search for people born in January 1980, they may attempt to use the following criteria:

Person : Date of Birth >= 01/01/1980 And
Person : Date of Birth <= 31/01/1980

This will return different results for a user in Dublin versus a user on the east coast of the US.

Cause:

Using the above citeria, the following SQL is used to query Person records.

User in CRM server time zone (UTC):

SELECT *
FROM (
    select ourOracleDerivedTable.*, ROWNUM ourOracleDerivedTableRowNum
    FROM (
        select * from vSearchListPerson
        WHERE (pers_dob2 >= TO_DATE('1980/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS'))
            AND (pers_dob2 <= TO_DATE('1980/01/31 23:59:00', 'YYYY/MM/DD HH24:MI:SS'))  
        ORDER BY UPPER(Pers_Lastname), UPPER(Pers_FirstName), Pers_PersonId) ourOracleDerivedTable
    WHERE ROWNUM < 11)
WHERE ourOracleDerivedTableRowNum >0

User in UTC -5:

SELECT * FROM (
    select ourOracleDerivedTable.*, ROWNUM ourOracleDerivedTableRowNum
    FROM (
        select * from vSearchListPerson
        WHERE (pers_dob2 >= TO_DATE('1980/01/01 05:00:00', 'YYYY/MM/DD HH24:MI:SS'))
            AND (pers_dob2 <= TO_DATE('1980/01/31 04:59:00', 'YYYY/MM/DD HH24:MI:SS'))  
        ORDER BY UPPER(Pers_Lastname), UPPER(Pers_FirstName), Pers_PersonId) ourOracleDerivedTable
    WHERE ROWNUM < 11)
WHERE ourOracleDerivedTableRowNum >0

Note that an offset is applied, even though the date-only field will always record the time as being midnight.


Status:

The issue has been raised to the development team for a fix in a future patch of Sage CRM. At present, the recommended workaround is to use the standard Find screens where a date-only field is to be queried, and a user is in a different time zone to the CRM server.

More information:

Both Sage CRM v7.1 and 7.2 are affected. The issue is reproducible for users using Oracle, or Microsoft SQL Server.