Found operand types 'Edm.DateTime' and 'Edm.String' for operator kind 'GreaterThanOrEqual'

3.2k Views Asked by At

Getting an error while trying to get the entities from Azure Table on basis of Timestamp which is of type Edm.DateTime:

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

enter image description here Code:

let res: IQueryPrePostAuthResponse[] = [];
let query = new TableQuery();
query = query.where('PartitionKey eq ?', searchQuery.user_id);

const entGen = TableUtilities.entityGenerator;
const dateFrom = entGen.DateTime(new Date(searchQuery.date_from));
const dateTo = entGen.DateTime(new Date(searchQuery.date_to));
query = query.and(
 'Timestamp >= ? and Timestamp <= ?',
  dateFrom,
  dateTo,
);

As in code both variables(dateFrom, & dateTo) are of DateTime type and Timestamp is of DateTime type. According to Odata Docs link query comparison operator, The data types on both sides of a comparison operator must be compatible. Here is my case both are the same, been then I am getting an error.

2

There are 2 best solutions below

0
On

When I print dateTo and dateFrom before sending it to query.

const entGen = TableUtilities.entityGenerator;
const dateFrom = entGen.DateTime(new Date(searchQuery.date_from));
const dateTo = entGen.DateTime(new Date(searchQuery.date_to));
console.log(dateFrom);
console.log(dateTo);

I got this in the output:- enter image description here After debugging a lot, I got to know that the way in which I was sending dateFrom and dateTo was not correct.
The correct way is dateFrom._ and dateTo._. But this should be mentioned in Azure Table or OData Query but it was absent there.

According to Odata Docs link query comparison operator, The data types on both sides of a comparison operator must be compatible.

This was creating confusion as getting error even the type of both are same. The correct code is below:-

let res: IQueryPrePostAuthResponse[] = [];
let query = new TableQuery();
query = query.where('PartitionKey eq ?', searchQuery.user_id);

const entGen = TableUtilities.entityGenerator;
const dateFrom = entGen.DateTime(new Date(searchQuery.date_from));
const dateTo = entGen.DateTime(new Date(searchQuery.date_to));
query = query.and(
 'Timestamp >= ? and Timestamp <= ?',
  dateFrom._,
  dateTo._,
);

Now I was able to query the entities from the Azure table on basis of Timestamp property. enter image description here

0
On

When filtering by a date field, do not put the date value in quotes.

eg. this is WRONG:

http://localhost:7048/XX/ODataV4/PostedSalesInvoice?Posting_Date&%24filter=Posting_Date+ge+'2023-01-01'&%24format=json 

This is correct:

http://localhost:7048/XX/ODataV4/PostedSalesInvoice?Posting_Date&%24filter=Posting_Date+ge+2023-01-01&%24format=json 

Also, I have an interesting JavaScript library for constructing odata URLs incase you're interested:

https://www.npmjs.com/package/navodata