function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MikeGillMikeGill 

MALFORM Query when trying to compare formula date fields

I am trying to perform a "not equal" on two date fields (formula derived)

 

select Id, MasterContract_Start_Date__c, Current_Contract_Start_Date__c
from Asset
where MasterContract_Start_Date__c != Current_Contract_Start_Date__c

I'm getting MALFORM query unexpected token when executing the above query using SoqlXplorer.

 

Compare to today works fine

 

select Id, MasterContract_Start_Date__c, Current_Contract_Start_Date__c
from Asset
where MasterContract_Start_Date__c != today

 What am I doing wrong, I should be able to do this I would have thought?

 

 

Best Answer chosen by Admin (Salesforce Developers) 
lakslaks

Hi Mike,

 

Here the problem is not due to date type. It is in your where clause:

 

select Id, MasterContract_Start_Date__c, Current_Contract_Start_Date__c from Asset where MasterContract_Start_Date__c != Current_Contract_Start_Date__c

 

As far as I know the above query wouldn't work because Current_Contract_Start_Date__c is also a field of the Asset object and you have not fetched it yet. So it doesn't know what is the value of Current_Contract_Start_Date__c with which you are comparing MasterContract_Start_Date__c.

 

I am not sure if there is any other solution, but I suggest you try the following work around.

 

You can create a formula field on Asset, say CompareDates__c:

 

IF(MasterContract_Start_Date__c != Current_Contract_Start_Date__c, 'True', 'False')

 

Now you can modify your query as:

 

select Id, MasterContract_Start_Date__c, Current_Contract_Start_Date__c from Asset where CompareDates__c = 'True'

 

Hope this solves your problem. If so please mark it as solution so that others may benefit. Else let me know what went wrong.

 

Regards,

Lakshmi.

 

 

 

All Answers

IspitaIspita

Hi Mike,

It is possible that one of the arguments is date while the other is of type datetime. Please to check this aspect for finding the reason of failure. Also I would suggest that you run the same query in salesforce "syetem log" :-

  • as such
  • by hard coding the second value of date with a value from the existing record, it sems if datatype mismatch is not the reason for failure some special character must be causing the issue , so generating the query to be run as a string and then checking it will help you identify the root cause.

Hope this helps. 
MikeGillMikeGill

Thanks for your response.

 

I have checked both fields are type date.

 

Running this in system log

 

String q = 'select Id, MasterContract_Start_Date__c, Current_Contract_Start_Date__c from Asset where MasterContract_Start_Date__c != Current_Contract_Start_Date__c';
Database.query(q);

 

 Gives unexpected token too. Strange isn't it
lakslaks

Hi Mike,

 

Here the problem is not due to date type. It is in your where clause:

 

select Id, MasterContract_Start_Date__c, Current_Contract_Start_Date__c from Asset where MasterContract_Start_Date__c != Current_Contract_Start_Date__c

 

As far as I know the above query wouldn't work because Current_Contract_Start_Date__c is also a field of the Asset object and you have not fetched it yet. So it doesn't know what is the value of Current_Contract_Start_Date__c with which you are comparing MasterContract_Start_Date__c.

 

I am not sure if there is any other solution, but I suggest you try the following work around.

 

You can create a formula field on Asset, say CompareDates__c:

 

IF(MasterContract_Start_Date__c != Current_Contract_Start_Date__c, 'True', 'False')

 

Now you can modify your query as:

 

select Id, MasterContract_Start_Date__c, Current_Contract_Start_Date__c from Asset where CompareDates__c = 'True'

 

Hope this solves your problem. If so please mark it as solution so that others may benefit. Else let me know what went wrong.

 

Regards,

Lakshmi.

 

 

 

This was selected as the best answer
MikeGillMikeGill

Thanks Lakshmi - that makes sense now

 

Good call with the formula field.