cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rizkyaditya
Resolver III
Resolver III

Patch datetimeoffset Column Type in SQL from Power Apps only show the Date

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi @rizkyaditya ,

 

I did a test for you .But i didn't encounter your problem.

v-xiaochen-msft_0-1622183277991.png

v-xiaochen-msft_1-1622183306606.png

v-xiaochen-msft_2-1622183329450.png

 

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.

 

 

 

View solution in original post

4 REPLIES 4
v-xiaochen-msft
Community Support
Community Support

Hi @rizkyaditya ,

 

I did a test for you .But i didn't encounter your problem.

v-xiaochen-msft_0-1622183277991.png

v-xiaochen-msft_1-1622183306606.png

v-xiaochen-msft_2-1622183329450.png

 

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?

v-xiaochen-msft
Community Support
Community Support

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.

 

 

 

Hello @v-xiaochen-msft ,

 

Clear explanation!

Thank you for the example for me to consider.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (5,239)