cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Importing OData DateTime fields results in CrmDateTime error

Hi,

 

When importing a OData v4 compliant data feed that includes DateTime or DateTimeOffset properties as a Dataflow, import errors are received such as: 

"Reason: Bad Request, Error code: 0x80040239, Message: DateTime is less than minumum value supported by CrmDateTime. Actual value: 01/01/0001 00:00:00, Minimum value supported: 01/01/1753 00:00:00"

 

This can be reproduced by using the Northwind reference V4 source from the OData specification:

https://services.odata.org/V4/Northwind/Northwind.svc/

 

Process to reproduce:

Create new Dataflow (with any name)

Use "https://services.odata.org/V4/Northwind/Northwind.svc/" as the source, click "Next"

Choose the "Employees" endpoint. Note that the data loads correctly into PowerQuery, and click "Transform Data"

Click "Next" on the transform data screen

Choose "Load to new entity" on the Map entities screen, leaving all the defaults in place and choose Next

Click "Create" to create the import

 

After a few moments, observe that the data feed import has failed. There does not appear to be any workaround for this problem on the OData side.

7 REPLIES 7
Highlighted
Microsoft
Microsoft

Re: Importing OData DateTime fields results in CrmDateTime error

Hi @27pchrisl This looks like a permission problem. I have 2 environments for testing it. My personal environment I'm the administrator and I was able to follow the steps you provided without any issues. Data Flow has been created successfully.

 

danvarga_0-1596824098061.png

In the second environment I'm not the owner or the administrator and I'm getting this issue after few seconds:

 

danvarga_1-1596824162163.png

Issue details:

There was an error while creating this entity. Details: Failed to create entity with logical name cr38e_employees and object type code -1. Exception: Microsoft.Crm.CrmSecurityException: SecLib::CheckPrivilege failed. User: 99324e48-7394-ea11-a811-000d3a579806, PrivilegeName: prvCreateEntity, PrivilegeId: 341e3ebf-74b8-4335-84f3-7f617bb7d081, Required Depth: Basic, BusinessUnitId: a47f0694-a911-e911-a98f-000d3a1c9b20, MetadataCache Privileges Count: 1731, User Privileges Count: 692 at Microsoft.Crm.BusinessEntities.SecurityLibrary.ThrowPrivilegeDeniedException(IUser user, RolePrivilege rolePrivilege, ExecutionContext context) at Microsoft.Crm.BusinessEntities.SecurityLibrary.GetMaxPrivilegeDepthForUserAcrossBusinessUnitsInternal(Guid user, Guid privilege, ExecutionContext context, Guid& businessUnitId) at Microsoft.Crm.BusinessEntities.SecurityLibrary.CheckPrivilegeGlobalDepth(Guid user, Guid privilege, ExecutionContext context) at Microsoft.Crm.Metadata.EntityService.<>c__DisplayClass35_0.<CreateInternalHelper>b__1() at Microsoft.Crm.SqlTelemetryHelper.LogSqlTimes(Action action, String operationName) at Microsoft.Crm.Metadata.EntityService.CreateInternalHelper(EntityCreateInfo entityInfo, MetadataHelper metadataHelper, ExecutionContext context). [Entity: Employees]

 

I'm not getting any issue related to the date.

Highlighted
Regular Visitor

Re: Importing OData DateTime fields results in CrmDateTime error

Hi @danvarga, appreciate your looking into this!

 

Can you confirm that your Dataflow imports successfully, and in the CSV log generated in the "Refresh history" after import there are no errors, and that the CSV indicates that 9 records were correctly upserted?

 

I get 2 records upserted, and 7 non-importable with the error message I provided previously. I have attached a screenshot of the two import runs on this particular flow (both showing the same errors), and the CSV I got back from the import log (converted to xlsx, since I cannot attach CSV here).

 

Thanks!

 

 - Chris

Highlighted
Microsoft
Microsoft

Re: Importing OData DateTime fields results in CrmDateTime error

@27pchrisl I'm not sure if I'm missing any step, but everything looks fine for me.

 

danvarga_0-1596826628418.png

 

danvarga_1-1596826782606.png

 

 

Highlighted
Advocate II
Advocate II

Re: Importing OData DateTime fields results in CrmDateTime error

Hi @27pchrisl

 

We are based in the UK and have encountered the same problem with Dataflows/Power Query and dates not in the US format.  The work around that solves the problem for us is, in Power Query, to change Options -> Project Options ->Locale from English (United Kingdom) to English (United States).

 

It doesn't make logical sense, but it solves the issue.  If you are in the US or using US format dates, please ignore this suggestion. 

Highlighted
Advocate II
Advocate II

Re: Importing OData DateTime fields results in CrmDateTime error

I should add that it is dates where the first characters in UK format (Day) are greater than 12. So, 29/12/2019 fails, but 08/07/2020 is OK because Dataflow thinks 08 is the month.

Highlighted
Regular Visitor

Re: Importing OData DateTime fields results in CrmDateTime error

Hi,

 

Yes, changing the Locale to US does cause the import to succeed. So that's good news! Thanks @LooseChippings 

 

I believe it points to an issue somewhere downstream then, since OData DateTimeOffsets are always in ISO8601 format and don't change format based on locale. It would look like it's a problem with the service that PowerQuery is pushing into, since PQ itself does load the dates correctly as "Date/Time/Zone". PowerBI also has no issue with the feed when using that on the desktop.

 

https://services.odata.org/V4/Northwind/Northwind.svc/Employees?$select=HireDate&$format=application...

 

Is that your understanding as well?

 

Cheers,

 

 - Chris

 

Highlighted
Advocate II
Advocate II

Re: Importing OData DateTime fields results in CrmDateTime error

Yes. It is not OData and occurs when using any Dataflow connector.  This is not new.  We first encountered the problem around three years ago.

 

We have British Summer Time, when the clocks go back one hour between March and October, and PowerBI does not recognise this change.  So 2020-08-07 00:00:00 becomes 2020-08-06 11:00:00 in Power BI.  This is also a known issue and we add one hour to dates in Power Query.

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,845)