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.

It works perfectly, thank you very much

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (3,558)