Share via

Null not accepted for date column in Dataverse

Van Haver, Jan 5 Reputation points
2023-07-28T10:36:14.2166667+00:00

We're using a logic app to bring data via Azure Service Bus into Dataverse. Some of the date columns in the JSON we create from the messages are of type null when no date is transmitted, e.g.

"TerminationDate": null,

to process this in either a Dataverse "Create a row" or "Update a row" action, we're using the following:

if(empty(body('Parse_JSON')?['TerminationDate']),null,parseDateTime(body('Parse_JSON')?['TerminationDate']))

but that leads to the following error:

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

Any ideas how to tell Dataverse that there is no value to process in these cases?

Azure Logic Apps
Azure Logic Apps
An Azure service that automates the access and use of data across clouds without writing code.
2,888 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mike Urnun 9,761 Reputation points Microsoft Employee
    2023-07-31T18:50:37.6833333+00:00

    Hello @Van Haver, Jan - Thanks for reaching out!

    You can use the coalesce function to replace null values with a default value. In this case, you can replace null values with a minimum date value supported by Dataverse. The minimum date value supported by Dataverse is January 1, 1753, 00:00:00. You can use the DateTime.Parse method to convert the date string to a DateTime object. Here's an example of how to use the coalesce function to replace null values with a minimum date value:

    coalesce(parseDateTime(body('Parse_JSON')?['TerminationDate']), DateTime.Parse("1753-01-01T00:00:00Z"))

    In this example, body('Parse_JSON')?['TerminationDate'] is the date string that you want to convert to a DateTime object. The parseDateTime function converts the date string to a DateTime object. The coalesce function checks if the result of the parseDateTime function is null. If it is null, the function returns the minimum date value supported by Dataverse.

    You can use this expression in the "Create a row" or "Update a row" action to process null values for date columns in Dataverse.

    For more information on the coalesce function and the parseDateTime function, you can refer to the following Azure documentation:

    I hope this helps! Let me know if you have any further questions.

    0 comments