Answered

Datetime in Model-Driven-App with Virtual Table

(0) Share

Report

Posted on by Braaank

Hello,

I have a created a virtual table with a table in SQL Server as datasource to create a model-driven-app that will allow users to maintain some records. There are around 50 columns most of them varchar, int, bit and datetime.

The read and update records work, but I'm having issues to create new records. The following error is thrown:

"The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value."

I don't have any datetime2 type in my SQL Server (as I know it is not supported by Virtual tables). As a remark, all datetime columns allow null.

I have the impresion that dataverse is trying to send the default value "0001-01-01" instead of null and that's causing the issue, is there anyway I can make it work?

Thank you very much in advance.

Best regards,

Categories:

Building Power Apps

Microsoft Dataverse with Power Apps

General topics

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Suggested answer

Pstork1 69,363 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I don't think there is any way around that problem since you are using a Dataverse Virtual table. Since you can't force it to send a null, I would suggest defaulting the datetime fields you do have to 1753-01-01. That should be the earliest available date for a SQL table.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
Verified answer

Braaank 43 on at

Like (0)

Report
Copy link

Link copied!

Thank you very much for the answers.

At the end I was able to solve the issue. Apparently there was a datetime column in SQL Server which was not allowing NULL value but has as default value getdate(). I was able to sort out the issue by removing that column in Dataverse so it doesn't even send it during the creation of the record. The only disadvantage would be that I won't be able to retrieve it, but that's not a requirement at this point.

Was this reply helpful? Yes No