Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Importing OData DateTime fields results in CrmDateTime error



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:


Process to reproduce:

Create new Dataflow (with any name)

Use "" 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.


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.



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



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.

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).




 - Chris

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







Advocate II
Advocate II

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. 

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.



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.$select=HireDate&$format=application...


Is that your understanding as well?




 - Chris


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

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.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,976)