Hello Power Apps Community,
I have situation like this :
1. My datasource is sql server
2. I create new row to that datasource on daily basis using power automate (it is some kind of daily maintenance job)
3. The datasource have column name CreatedAt and FinishedAt, those two columns are datetimeoffset type (based on this article Working with date/time values and SQL Server
4. The CreatedAt column values are generated from power automate, and it shows good result either in powerapps or in the sql table
View in data source :
2021-05-28 09:56:13.2633333 +07:00
View in powerapps label :
2021-05-28 09:56
5. The problem happens when i tried to patch the FinishedAt column.
I use this to patch it from the powerapps :
Patch(myDataSource, LookUp(myDataSource, Id =ThisItem.Id), {FinishedAt: Now()})
It did now show any error and the data also patched to the table.
But it only contain the date, without any information for the time and also the offset like this :
2021-05-28 00:00:00.0000000 +00:00
My question is, how patch back to a datetimeoffset column correctly from powerapps, so it will hold the complete dataset starting from date to the offset? (eg. 2021-05-28 09:56:13.2633333 +07:00)
Thank you!
Solved! Go to Solution.
Hi @rizkyaditya ,
I did a test for you .But i didn't encounter your problem.
It should be noted that if you save the date and time value from powerapps to the datetimeoffset column, the value is UTC time (+0).
Please check if the data type of your FinishedAt column is correct.
In addition, you couold try to remove the sql server connection and re-add it to the canvas app.
Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.
Hi @rizkyaditya ,
I did a test for you .But i didn't encounter your problem.
It should be noted that if you save the date and time value from powerapps to the datetimeoffset column, the value is UTC time (+0).
Please check if the data type of your FinishedAt column is correct.
In addition, you couold try to remove the sql server connection and re-add it to the canvas app.
Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.
Hello @v-xiaochen-msft ,
The data type is already the same as yours (datetimeoffset(7)).
Maybe it is because the connection between powerapps and the sql.
After i reconnect it, the problem gone and the patched value is correct.
Thank you for this reminder : "It should be noted that if you save the date and time value from powerapps to the datetimeoffset column, the value is UTC time (+0)."
Aside from my original question, which one do you suggest better to be used as sql data type in terms of handling inside the powerapps : datetime of datetimeoffset?
Hi @rizkyaditya ,
First of all, the now() function of powerapps refers to the local time.
For example:
If you are in UK and the time is 3:00 PM , the value of now() function is 3:00 PM.(Daylight saving time is not considered here)
At the same time ,if you are in china (+8), the value of now() function is 11:00 PM.
The advantage of the 'datetimeoffset' is to automatically convert all the time into UTC time.
So, if your users come from all over the world, then using datetimeoffset is a good choice.
Because you don’t need to modify your formula according to their time zone.
If your users have the same time zone, then using 'datetime' is also a good choice.
In addition, if your users have different time zones and you want to use 'datetime', then you need to modify your formula according to the time zone.
Finally, if you don’t want to consider too many conditions, I suggest you use 'datetimeoffset'.
Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.
User | Count |
---|---|
257 | |
110 | |
90 | |
51 | |
44 |