In my previous blog I covered the behavior of the date and time fields within the Power App Platform. This mainly focused on the behavior of displaying the data, but what if you must update those fields through the Web API (or CDS). What value do you have to send to get the correct value in the database?
Just a little recap, the following date/time fields are (currently) supported within CDS (Power App Platform):
- Date and Time (Time Zone Independent);
- Date and Time (User Local);
- Date Only (Time Zone Independent);
- Date Only (User Local);
- Date Only (Date Only).
All the date and time fields in CDS are stored in UTC and when retrieved through the Web API also retrieved in UTC. So, if we want to store the data in CDS we should submit the UTC value. Isn’t that hard is it?
This means that submitting data through the Web API doesn’t take the time zone of the user submitting the data into account. This is very useful as we can determine exactly what date and time we need to submit to the system and we know that the Web API is always giving us the UTC values (please be aware that the DateTime object in some coding languages, like C#, implicitly contain a time zone value which can lead to strange results).
So, to give an example. If we want to have the same “GMT -12” record as in the previous blog post, we should submit the following values to the Web API. This can be done by any user, living in any time zone.
{
"new_name": "GMT -12 (Web API)",
"new_dateonlydateonly": "2019-12-18",
"new_timezoneindependentdateonly": "2019-12-18T00:00:00Z",
"new_timezoneindependentdateandtime": "2019-12-18T00:00:00Z",
"new_userlocaldateonly": "2019-12-18T12:00:00Z",
"new_userlocaldateandtime": "2019-12-18T12:00:00Z"
}
To get the outcome we expect, the values should be submitted as follows depending on the field type and behavior of the field.
| Field Type | Behavior | Value to submit |
|---|---|---|
| Date and Time | Time Zone Independent | Just enter the date & time you want everyone to see. |
| Date Only | Time Zone Independent | Just enter the date (& time) you want everyone to see. Note that the time isn’t shown in the native clients (it is weirdly enough returned when retrieved through the web API). |
| Date Only | User Local | Convert (in code) the user local time to UTC and submit the UTC value. When retrieved, you get the UTC value. When displayed in the native clients Dynamics will convert the UTC value to the local value of the user. |
| Date and Time | User Local | Convert (in code) the user local time to UTC and submit the UTC value. When retrieved, you get the UTC value. When displayed in the native clients Dynamics will convert the UTC value to the local value of the user. |
| Date Only | Date Only | Just submit the date in YYYY-mm-dd format. |
I hope you can use this information. Please let me know if there are any further questions or unexpected results.