Calling on-premise ODATA from SAP CAP

[sandeepmalhotra]

Hello Folks,

I am calling on-premise ODATA in SAP CAP ( via destination) and got the issues while using filter on Edm.DateTimeOffset property

Following are steps I have done 

1 Imported the edmx via cds import

2 Created the wrapper Entity set in the SAP CAP service

3 In on handler call the function cds.connect.to to read data

I tried following URL but got error

URL	Issue
EntitySet?fitler=someProperty eq '00206566' and (startDate gt datetime 2021-11-25T00:00:00 and endDate lt datetime 2021-11-26T00:00:00 )	Property 'datetime' does not exist in type
$filter=someProperty eq '00204966' and (startDate ge 2021-11-25T00:00:00 and endDate le 2021-11-25T00:00:00)	The type 'Edm.DateTimeOffset' is not compatible to 'Edm.Date'

In on-premise system following URL works perfectly

EntitySet?$filter=someProperty eq '00204966'and startDate ge datetime'2021-11-25T00:00:00' and endDate le datetime'2021-11-25T00:00:00' & $format=json

Kindly let me know 

Thanks in advance

P.S Please make a note that am using odata v4 in SAP CAP and on-premise ODATA is v2. Also data type of date field is Edm.Date with precision 7 in on-premise odata but it is Edm.DateTimeOffset in SAP CAP odata metadata

[sandeepmalhotra · Accepted solution]

I found the cause SAP CAP framework send the date 2024-05-23T00:00Z to SAP on-premise system But SAP on-premise system accepts date 2024-05-23T00:00 ( without Z) . So I removed the Z from the date before calling on -premise . Here is the sample code 

    this.on(["READ"], "TIMEENTRIES", async (req: any) => {
      let { where } = req.query.SELECT;
      if (Array.isArray(where)) {
        //Remove Z from the date string as it is not accepted in SAP on-premise system
        // Input 2024-05-23T00:00Z Output 2024-05-23T00:00
        where.forEach((row: any) => {
          if (typeof row === "object" && "val" in row && DateUtil.isDateValid(row.val)) {
            row.val = row.val.toString().replaceAll("Z", "");
          }
        });
      }
      const timeCostEntry = await cds.connect.to("ZTIMECOSTENTRY_SRV");
      return timeCostEntry.run(req.query);
    });

[Dinu]

By default CAP serves oData V4. The representation of datetime literals in URL (and payload) is different in oData V4 from oData V2. Use the syntax for oData V4 for oData V4 services. See OData Version 4.01. Part 2: URL Conventions (oasis-open.org). Here are some samples from it.

DateValue eq 2012-12-03
DateTimeOffsetValue eq 2012-12-03T07:16:23Z
DurationValue eq duration'P12DT23H59M59.999999999999S'
DurationValue eq 'P12DT23H59M59.999999999999S'
TimeOfDayValue eq 07:59:59.999

[sandeepmalhotra]

Thanks for your help. When I tried. ?$filter=start Date ge 2021-11-25T00:00:00Z and endDate le 2021-11-25T00:00:00Z. I got Error during request to remote service: Invalid token detected at position 12

[junwu]

seems you are missing ' in your odata url startDate gt datetime 2021-11-25T00:00:00

[sandeepmalhotra]

I got the error Property 'datetime' does not exist in type after using $filter=someProperty eq '00204966' and (startDate ge datetime'2021-11-25T00:00:00' and endDate le datetime'2021-11-25T00:00:00')

[junwu]

how about this? startDate ge 2021-11-25T00:00:00Z

[sandeepmalhotra]

I found the cause SAP CAP framework send the date 2024-05-23T00:00Z to SAP on-premise system But SAP on-premise system accepts date 2024-05-23T00:00 ( without Z) . So I removed the Z from the date before calling on -premise .