cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
keshamer
Advocate III
Advocate III

issue with sql time format with azure db

Hi, I made an app sending data to azure sql db

I'm french and when I enter the time type 13:30 in a row in the SQL db I see 13:30:00 (time type). Alright it works but...

When I try to make an app to modify it, It is a text type with "PT13H30M".

 

I can't do anything with that. Hour() an minute() fonction doesn't works at all I tryed to text() it but it doen't work.

 

I really need to use hour() and minute() functions to modify it. Can someone help me to transform the text to a time type?

 

thank you

 

kind regards

1 ACCEPTED SOLUTION

Accepted Solutions
CarlosFigueira
Power Apps
Power Apps

This is a current limitation with time columns in SQL server - they're exposed to PowerApps as a string with the format that you're seeing. You can use this expression to convert between that format an a Time value in PowerApps (replace 'ThisItem.Time' with the name of the value that contains the time column):

Time(
    Value(Mid(ThisItem.Time, 3, Find("H", ThisItem.Time) - 3)), // Hour component
    If(
        IsBlank(Find("M", ThisItem.Time)), // Check if there is a minute component
        0,                                 // If not, the value is zero
        Value(                             // Otherwise take the substring between the rest of the value
            Left(                          //     after the hour component and the 'M' indicator
                Mid(
                    ThisItem.Time,
                    If(
                        IsBlank(Find("H", ThisItem.Time)),
                        3, Find("H", ThisItem.Time) + 1)),
                Find(
                    "M",
                    Mid(
                        ThisItem.Time,
                        If(
                            IsBlank(Find("H", ThisItem.Time)),
                            3,
                            Find("H", ThisItem.Time) + 1))) - 1))),
    If(
        IsBlank(Find("S", ThisItem.Time)), // Check if there is a second component
        0,                                 // If not, the value is zero
        Value(                             // Otherwise take the substring after the minute or hour indicator,
            Substitute(                    //     remove the 'S' indicator, then take the value
                If(
                    IsBlank(
                        Find(
                            "M",
                            Mid(
                                ThisItem.Time,
                                If(
                                    IsBlank(Find("H", ThisItem.Time)),
                                    3,
                                    Find("H", ThisItem.Time) + 1)))),
                    Mid(
                        ThisItem.Time,
                        If(
                            IsBlank(Find("H", ThisItem.Time)),
                            3,
                            Find("H", ThisItem.Time) + 1)),
                    Mid(ThisItem.Time, Find("M", ThisItem.Time) + 1)),
            "S",
            ""))))

Yes, this is quite complicated. Please consider creating a new feature request in the PowerApps Ideas board to have a better integration between SQL time columns and PowerApps.

Another workaround is to use a column type other than time to store the data. Using a text column (nvarchar(8), for example, to store 'HH:mm:ss') is a common one.

View solution in original post

2 REPLIES 2
CarlosFigueira
Power Apps
Power Apps

This is a current limitation with time columns in SQL server - they're exposed to PowerApps as a string with the format that you're seeing. You can use this expression to convert between that format an a Time value in PowerApps (replace 'ThisItem.Time' with the name of the value that contains the time column):

Time(
    Value(Mid(ThisItem.Time, 3, Find("H", ThisItem.Time) - 3)), // Hour component
    If(
        IsBlank(Find("M", ThisItem.Time)), // Check if there is a minute component
        0,                                 // If not, the value is zero
        Value(                             // Otherwise take the substring between the rest of the value
            Left(                          //     after the hour component and the 'M' indicator
                Mid(
                    ThisItem.Time,
                    If(
                        IsBlank(Find("H", ThisItem.Time)),
                        3, Find("H", ThisItem.Time) + 1)),
                Find(
                    "M",
                    Mid(
                        ThisItem.Time,
                        If(
                            IsBlank(Find("H", ThisItem.Time)),
                            3,
                            Find("H", ThisItem.Time) + 1))) - 1))),
    If(
        IsBlank(Find("S", ThisItem.Time)), // Check if there is a second component
        0,                                 // If not, the value is zero
        Value(                             // Otherwise take the substring after the minute or hour indicator,
            Substitute(                    //     remove the 'S' indicator, then take the value
                If(
                    IsBlank(
                        Find(
                            "M",
                            Mid(
                                ThisItem.Time,
                                If(
                                    IsBlank(Find("H", ThisItem.Time)),
                                    3,
                                    Find("H", ThisItem.Time) + 1)))),
                    Mid(
                        ThisItem.Time,
                        If(
                            IsBlank(Find("H", ThisItem.Time)),
                            3,
                            Find("H", ThisItem.Time) + 1)),
                    Mid(ThisItem.Time, Find("M", ThisItem.Time) + 1)),
            "S",
            ""))))

Yes, this is quite complicated. Please consider creating a new feature request in the PowerApps Ideas board to have a better integration between SQL time columns and PowerApps.

Another workaround is to use a column type other than time to store the data. Using a text column (nvarchar(8), for example, to store 'HH:mm:ss') is a common one.

View solution in original post

It works perfectly, thank you very much

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,519)