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.