Demonstration - Building a sample app to store datetime values
To demonstrate this topic, we'll create a table in SQL Server to store
meeting times. The key step is to set the 'meeting date' column to
the data type datetimeoffset as shown in the screenshot beneath.
From Power Apps, we can now create controls on a screen, and patch a new record to the table like so:
This formula adds a new record to the Meeting table based on the values that the user enters in the text input controls.
What happens when a user adds a record?
Let's examine what happens when a user adds a record. From a computer with the Windows time zone set to Pacific time (UTC - 8hrs at the time of writing), this is what happens when we attempt to add a record with a target time of 3 PM PST.
In the call to Patch, Power Apps uses the time zone setting on the user device to calculate the offset, and it passes the UTC value to SQL Server (ie, 2021-12-09T23:00:00.000z). We can see this when we monitor the app.
Following the addition of the record, we can inspect the value in SQL Server and confirm that our formula saves the date time value correctly (e.g. with a UTC time element of 23:00)