cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joh
Frequent Visitor

Dateflows issues with Dates

We have created a Dataflow that connects to on-prem sql-server trough a gateway. The purpose is to store the date in Dataverse for further use.

 

Error message in Dataflow

 

{"error":{"code":"0x0","message":"An error occurred while validating input parameters: Microsoft.OData.ODataException: Cannot convert the literal '2020-02-10T00:00:00+00:00' to the expected type 'Edm.Date'. ---> System.FormatException: String '2020-02-10T00:00:00+00:00' was not recognized as a valid Edm.Date.

 

Part of the detailed error:

 

https://xxxx.crm4.dynamics.com/api/data/v9.1/cr8f7_xxxx(cr8f7_index=1){
    ...
    "cr8f7_chdt": "2020-02-10T00:00:00+00:00",
    ...
  }

 

 

The field/column in Dataverse is created as Date Only. We expect the format to be : yyyy-MM-dd

 

Using the web api in Chrome, we get the following output, as expected:

 

{
  "@odata.context": "https://xxxx.crm4.dynamics.com/api/data/v9.1/$metadata#cr8f7_xxxx/$entity",
  "@odata.etag": "W/\"51229613\"",
  ...
  "cr8f7_chdt": "2020-02-10",
  ...
}

 

 

Question is, how can we work around this issue?

 

Thanks!

 

 

2 REPLIES 2
ChrisPiasecki
Super User
Super User

Hi @joh,

 

For the Dataverse column, is the behaviour set to Date Only as well? If so, you should add a transform in your dataflow to send only the Date part (YYYY-mm-dd). 

 

If it's set to User Local, then try tweaking the format.  E.g. 2020-02-10T00:00:00.000Z

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Hi @ChrisPiasecki  and thanks for your reply!

 

What we have done is to convert the date field we get from the SQL-server to "yyyy-MM-dd" as text in PowerQuery. Then we use that field to load to Dataverse as date, this works.

 

The error happens when an upsert/compare is done and Dataverses reads the data as the long format causing the error. We moved to store it as text for now to avoid the date issue.

 

 

 

 

 

 

 

 

 

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (3,452)