Saturday, August 6, 2016

A binary operator with incompatible types was detected. Found operand types 'Edm.DateTimeOffset' and 'Edm.String' for operator kind 'GreaterThanOrEqual'


Exception Details

Type1 A binary operator with incompatible types was detected. Found operand types 'Edm.DateTimeOffset' and 'Edm.String' for operator kind 'GreaterThanOrEqual'

Type2 Operator 'ge' incompatible with operand types 'System.Nullable`1[[System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]' and 'System.String' at position 10.


Usually the error comes when there is a syntax error in the query we designed and dealing with date time filters in Microsoft Dynamic CRM .

Now how to use Date Filter in Web API or O Data Query in Microsoft Dynamic CRM ?

Let’s consider a case where we want to query all sales order with booked date in year 2015.

SQL select query for this would be

Select *
from SalesOrder
Where new_BookedDate >= '2015-01=01' And new_BookedDate <= '2015-12-31'


Equivalent Fetch Xml would be


    <fetch mapping="logical" version="1.0">
      <entity name="SalesOrder">
        <all-attributes />
        <filter>
          <condition attribute="new_BookedDate" operator="ge" value="2015-01=01" />
          <condition attribute="new_BookedDate" operator="le" value="2015-12-31" />
        </filter>
      </entity>
    </fetch>
     

OData Syntax for Query would be

// Getting Server Context
var serverUrl = Xrm.Page.context.getClientUrl();

// Defining the OData complete Url
serverUrl = serverUrl + "/XRMServices/2011/OrganizationData.svc/" ;

// Name of the entity
serverUrl = serverUrl + "SalesOrderSet?";  

// Filter Criteria with Date
serverUrl = serverUrl + "$filter=new_BookedDate ge datetime'2015-01-01' and new_BookedDate 



Web API Syntax for Query would be

// Getting Server Context
var serverUrl = Xrm.Page.context.getClientUrl();

serverUrl = serverUrl + "/api/data/v8.1/" ; // Defining the OData complete Url
serverUrl = serverUrl + "salesorders?";    // Name of the entity

// Filter Criteria with Date
serverUrl = serverUrl + "$filter=new_BookedDate ge 2015-01-01 and new_BookedDate le 2015-12-31"


I hope the information is useful !!!

1 comment:

Unknown said...

Hi,

Is there possible way to filter odata based from date today? It seems in CRM 2016(odata v8) year() or now() is not supported.

I was wondering if there is any way possible to do this.