cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
keshamer

integration of SQL time columns in PowerApps

Submitted by Advocate III on ‎10-25-2018 01:48 AM

Hi we can insert time in sql but powerapps can't take it back to modify it. without a long function.

thank you to @CarlosFigueira for the function anyway:

 

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",
            ""))))

 

 

Status: New
See more ideas labeled with:
2 Comments (2 New)
Comments
WPB
Kudo Kingpin
Kudo Kingpin
‎10-30-2018 07:42 AM

YES!

We cannot filter a table based on dates either.

keshamer
Advocate III
Advocate III
‎11-15-2018 11:57 AM

@WPB

Yes we can but you have to practise some SQL ^^. This problem is due to delegation with dates. You can transfor dates in Int with this function in sql

datepart(year,[Voy_Jourch])*(10000)+datepart(month,[Voy_Jourch])*(100))+datepart(day,[Voy_Jourch])

with this you can transform 11/15/2018 in 181115. Now you can filter and order ^^