OData Filter based on Time failed partially

[Mojos]

Guys,

I tried to filter out with SharePoint list from 01 to 24 with current time HH, I have no issues with 10 onwards but have problem to filter 01 to 09

DispatchTime eq '@{outputs('Compose_-_Today_Time')}' or
DispatchTime eq @{formatDateTime(addHours(utcNow(),7),'HH')} or
DispatchTime eq '@{trim(outputs('Compose_-_Today_Time'))}' or

the result was :

"body": {
        "status": 400,
        "message": "The expression \"DispatchTime eq '09\n' or \nDispatchTime eq 09 or \nDispatchTime eq '9' or \nDispatchTime eq '9'\" is not valid. Creating query failed.\r\nclientRequestId: ad130b4f-f303-44f1-8de2-de210c7b4068\r\nserviceRequestId: 8dca3aa1-5014-6000-0032-6f7663bb0722"
    }
}

[SudeepGhatakNZ]

@Mojos ,

The error indicates that the filter expression contains newlines and possibly mismatched types. Try this

Add a Compose action to get the current time formatted as "HH"

formatDateTime(utcNow(), 'HH')

Add another Compose action to get the time 7 hours ahead, formatted as HH:

formatDateTime(addHours(utcNow(), 7), 'HH')

Use the outputs of these Compose actions in your filter query without newlines:

DispatchTime eq '@{outputs('Compose_Today_Time_Formatted')}' or DispatchTime eq '@

•  

[Mojos]

Hi @SudeepGhatakNZ, I've same setup, mentioned in first post .. have it as Compose and as Function into the OData filter, and again it works for 10-24 but not for 00 - 09

[SudeepGhatakNZ]

Can you provide the RAW input and Output of the action that's failing?

[Mojos]

Herewith failing raw data

input

{
    "host": {
        "connectionReferenceName": "shared_sharepointonline",
        "operationId": "GetItems"
    },
    "parameters": {
        "dataset": "https://xxxxxx.sharepoint.com/sites/xxxxxx",
        "table": "xxxxxx",
        "$filter": "DispatchTime eq '07\n' or \nDispatchTime eq 07 or \nDispatchTime eq '7' or \nDispatchTime eq '7'"
    }
}

output

{
    "statusCode": 400,
    "headers": {
        "Cache-Control": "max-age=0, private",
        "Vary": "Origin",
        "X-NetworkStatistics": "0,4194560,0,0,18905468,0,4194560,8",
        "X-SharePointHealthScore": "0",
        "X-MS-SPConnector": "1",
        "X-SP-SERVERSTATE": "ReadOnly=0",
        "DATASERVICEVERSION": "3.0",
        "SPClientServiceRequestDuration": "62",
        "SPRequestDuration": "63",
        "X-DataBoundary": "NONE",
        "X-1DSCollectorUrl": "https://mobile.events.data.microsoft.com/OneCollector/1.0/",
        "X-AriaCollectorURL": "https://browser.pipe.aria.microsoft.com/Collector/3.0/",
        "SPRequestGuid": "a16ec694-6adb-49cc-8ad8-ceea77953e99",
        "request-id": "a16ec694-6adb-49cc-8ad8-ceea77953e99",
        "MS-CV": "oTu5E4TAAGANOATPoifw2w.0",
        "Strict-Transport-Security": "max-age=31536000",
        "X-Frame-Options": "SAMEORIGIN",
        "Content-Security-Policy": "frame-ancestors 'self' teams.microsoft.com *.teams.microsoft.com *.skype.com *.teams.microsoft.us local.teams.office.com teams.cloud.microsoft *.office365.com goals.cloud.microsoft *.powerapps.com *.powerbi.com *.yammer.com engage.cloud.microsoft word.cloud.microsoft excel.cloud.microsoft powerpoint.cloud.microsoft *.officeapps.live.com *.office.com *.microsoft365.com *.stream.azure-test.net *.microsoftstream.com *.dynamics.com *.microsoft.com onedrive.live.com *.onedrive.live.com securebroker.sharepointonline.com;",
        "MicrosoftSharePointTeamServices": "16.0.0.25019",
        "X-Content-Type-Options": "nosniff",
        "X-MS-InvokeApp": "1; RequireReadOnly",
        "P3P": "CP=\"ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI\"",
        "X-AspNet-Version": "4.0.30319",
        "X-Powered-By": "ASP.NET",
        "Timing-Allow-Origin": "*",
        "x-ms-apihub-cached-response": "false",
        "x-ms-apihub-obo": "false",
        "Date": "Sat, 13 Jul 2024 00:00:45 GMT",
        "Content-Length": "306",
        "Content-Type": "application/json",
        "Expires": "Fri, 28 Jun 2024 00:00:46 GMT",
        "Last-Modified": "Sat, 13 Jul 2024 00:00:46 GMT"
    },
    "body": {
        "status": 400,
        "message": "The expression \"DispatchTime eq '07\n' or \nDispatchTime eq 07 or \nDispatchTime eq '7' or \nDispatchTime eq '7'\" is not valid. Creating query failed.\r\nclientRequestId: a16ec694-6adb-49cc-8ad8-ceea77953e99\r\nserviceRequestId: 13b93ba1-c084-6000-0d38-04cfa227f0db"
    }
}

Run history, doesn't work between 00-09