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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (42,863)