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

Dataflow Date Only field error

Hi,

 

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? 

 

Thank you. 

4 REPLIES 4
dpanek
New Member

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)

arggghhhhh
Frequent Visitor

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
eg
CONVERT(char(10), MyDateColumn,126)

mubaco22
Frequent Visitor

hi Rameshwar ,  are you resolved this issues? 
I am faceing the same error 

 

Could someone help here to resolve this issue? 

Thank you. 

arggghhhhh
Frequent Visitor

Convert your data to a string that looks like this '2021-03-29'  (eg yyyy-mm-dd)

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Kudoed Authors
Users online (57,518)