cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Converting time only field from UTC

Hello, I have a field in my SQL db that is time only. The date portion is stored in a different column because it is tied to a table. Also, the users will only want to enter the time portion into the form. I have been able to get the app to store the time of 10:15 am into the database (10:15:00.0000000).  When I view that value in a gallery it shows as PT10H15M. I am trying to get the text input field to reflect the value that is in the gallery as 10:15 am after they have submitted the form. I have tried various formulas using DateTimeValue, TimeValue, TimeZoneOffset, but I do not know how to handle it without the date portion. I have read the "time after time" and "party time" posts and I do not see anything I can use. Is there a way to use the timezoneoffset function with just the hour and minute?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @PaigeJ ,

Do you want to update time to sql and display time in powerapps?

Could you tell me this time field's data type?

I assume that it is time(7) data type.

I've made a similar test for your reference:

1)in my test, the time field named test3, my table named '[dbo].[testtime]'

2)insert a gallery to display the sql table

set the gallery's Items:

'[dbo].[testtime]'

if you set the label's Text to :

ThisItem.test3

It will display in this format:

PT12H45M10S

So you just need to get the number before "H","M" and "S"

 

3)try to set the label's Text:

Time(
Value(If("H" in ThisItem.test3,Match(ThisItem.test3,"\d+").FullMatch,0)),
Value(Substitute(If("M" in ThisItem.test3,Match(ThisItem.test3,"\d+M").FullMatch,0),"M","")),
Value(Substitute(If("S" in ThisItem.test3,Match(ThisItem.test3,"\d+S").FullMatch,0),"S",""))
)

Then this column will display in time format.

513.PNG

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Super User III
Super User III

Try following this post:
https://powerusers.microsoft.com/t5/Building-Power-Apps/Dealing-with-Time-from-SQL-and-doing-compari...

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Community Support
Community Support

Hi @PaigeJ ,

Do you want to update time to sql and display time in powerapps?

Could you tell me this time field's data type?

I assume that it is time(7) data type.

I've made a similar test for your reference:

1)in my test, the time field named test3, my table named '[dbo].[testtime]'

2)insert a gallery to display the sql table

set the gallery's Items:

'[dbo].[testtime]'

if you set the label's Text to :

ThisItem.test3

It will display in this format:

PT12H45M10S

So you just need to get the number before "H","M" and "S"

 

3)try to set the label's Text:

Time(
Value(If("H" in ThisItem.test3,Match(ThisItem.test3,"\d+").FullMatch,0)),
Value(Substitute(If("M" in ThisItem.test3,Match(ThisItem.test3,"\d+M").FullMatch,0),"M","")),
Value(Substitute(If("S" in ThisItem.test3,Match(ThisItem.test3,"\d+S").FullMatch,0),"S",""))
)

Then this column will display in time format.

513.PNG

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (13,152)