Power Platform Community Forum Thread Details

Hello all,

I'm building a function in a canvas app that executes a SQL stored procedure. The procedure is passed a row ID and what the row's last modified datetime should be, and if it finds a row where both match, it deletes it. It executes with no errors, but it outputs zero rows deleted and the row is still present in the SQL table. Everything works as intended when executed in SSMS.

The row in the SQL table has a last modified datetime of "2025-01-16 10:38:44.947". The record in PowerApps looks to have an accurate datetime of "1/16/2025, 10:38:44 AM"; even though the preview doesn't show milliseconds, it returns the correct epoch time when cast to a value. However, the stored procedure reports that it's being passed "2025-01-16 10:38:00.000". I don't have access to the SQL logs so can't tell if it's receiving zeroes or just no seconds or milliseconds.

I've tried passing the epoch time, but my version of SQL can't convert it to a datetime. Casting the datetime to text also omits seconds and milliseconds. I'm working on changing the stored procedure to accept an epoch time and convert it to a datetime using dateadd, but would rather not leave it like that.

Has anyone else encountered this before? And does anyone know of a better workaround than what I came up with?

Categories:

Building Power Apps

It sounds like you're encountering a precision issue with the datetime values being passed from PowerApps to your SQL stored procedure. This is a common problem, especially when dealing with milliseconds.

Here are a few suggestions that might help resolve this issue:

Ensure Millisecond Precision: SQL Server's DATETIME type rounds to increments of .000, .003, or .007 seconds. If millisecond precision is crucial, consider using DATETIME2 which offers higher precision.
Format Datetime Correctly: When passing datetime values from PowerApps, ensure they are formatted correctly. You can use the format yyyy-MM-dd HH:mm:ss.fff to include milliseconds.
Convert Epoch Time: If you decide to pass epoch time, you can convert it to datetime in SQL using DATEADD. Here's an example:

DECLARE @EpochTime BIGINT = 1673876324947; -- Example epoch time

DECLARE @DateTime DATETIME2 = DATEADD(MILLISECOND, @EpochTime % 1000, DATEADD(SECOND, @EpochTime / 1000, '1970-01-01'));

Check PowerApps Configuration: Ensure that PowerApps is correctly passing the datetime value with milliseconds. Sometimes, the issue might be with how PowerApps formats or sends the data.
Stored Procedure Adjustment: If changing the stored procedure to accept epoch time is feasible, it might be a reliable workaround. However, ensure that the conversion logic is robust and handles edge cases.
Debugging: Since you don't have access to SQL logs, consider adding logging within your stored procedure to capture the exact datetime values being passed. This can help identify if the issue is with the data being received or the comparison logic.

If the response is helpful to you, a like or mark as the correct solution. thank you so much!