I am facing an error while loading data into CDS/Dataverse entity/table. The issue is specific to Date datatype. Earlier, I had a column with DateOnly datatype with Behavior as "User local". At that time my Dataflow was working fine while loading data for this column.
Later on, I have changed this column's behavior to "Date only" so that it would store only date and no time component associated with it. However, due to this my Dataflow has started failing and throwing following error -
"Reason: Bad Request, Header x-ms-client-request-id f8b810e1-32ca-42c4-939d-db10dc647ff1, Error code: 0x0, Message: An error occurred while validating input parameters: Microsoft.OData.ODataException: Cannot convert the literal '2020-11-12T00:00:00Z' to the expected type 'Edm.Date'. ---> System.FormatException: String '2020-11-12T00:00:00Z' was not recognized as a valid Edm.Date."
I tried following ways to resolve, however there was no luck - (Please note that my source is Azure SQL Database. )
1. I have marked the datatype source column as DateOnly in Power Query, and assigned it to DateOnly field of CDS, still no luck.
2. I tried changing the datatype to text with format as "MM/dd/yyyy" and mapped to DateOnly field of CDS, still no luck.
3. I tried changing the datatype to text with format as "yyyy-MM-dd" and mapped to DateOnly field of CDS, still no luck.
Could someone help here to resolve this issue?
Hi, you might need to strip off the last 10 characters of your source field to remove the timestamp before passing it to CDS. Looks like even though you are formatting the source, it is still passing a null timestamp which CDS is complaining about. In SQL it should be something like RTRIM(10,yoursourcedatefield)
I had the same problem and got it working by updating the SQL Statement (power query advanced editor) to convert the date to string with the format of yyyy-mm-dd
Check out new user group experience and if you are a leader please create your group
We are excited to announce that Demo Extravaganza for 2021 has started!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Did you miss the call? Check out the recording here!