SAP Service Layer Odata Filter - Check Date Between From and To Date

[rahuljain257]

Hello Everyone,

How to filter date between from Date and To_Date via Odata Filter in Service Layer ?

Select * From SomeTable where EnteredDate between Col1 and Col2;

Thanks

Rahul Jain

[former_member390407 · Accepted solution]

Hi Rahul,

Why do you start with value, not with a property? Try to convert it from

$filter=EnteredDate lt Col1 and EnteredDate gt Col2

To

$filter=Col1 gt EnteredDate and Col2 lt EnteredDate

For example:

I query invoices (SBODEMOGB schema) with the request:

 https://address:50000/b1s/v1/Invoices?$select=CardCode,DocNum,DocDate,DocDueDate

And get the following:

{
    "odata.metadata": "https://192.168.200.50:50000/b1s/v1/$metadata#Invoices",
    "value": [
        {
            "DocNum": 1,
            "DocDate": "2006-01-22",
            "DocDueDate": "2006-02-21",
            "CardCode": "C20000"
        },
        {
            "DocNum": 2,
            "DocDate": "2006-01-27",
            "DocDueDate": "2006-02-26",
            "CardCode": "C30000"
        },
        {
            "DocNum": 3,
            "DocDate": "2006-02-11",
            "DocDueDate": "2006-04-11",
            "CardCode": "C40000"
        },
        ....
        {
            "DocNum": 20,
            "DocDate": "2006-06-11",
            "DocDueDate": "2006-08-11",
            "CardCode": "C23900"
        }
    ],
    "odata.nextLink": "/b1s/v1/Invoices?$select=CardCode,DocNum,DocDate,DocDueDate&$skip=20"
}

Now if I apply a filter in your way:

 https://address:50000/b1s/v1/Invoices?$select=CardCode,DocNum,DocDate,DocDueDate&$filter='20060130' gt DocDate and '20060130' lt DocDueDate

I get an empty response:

{
    "odata.metadata": "https://192.168.200.50:50000/b1s/v1/$metadata#Invoices",
    "value": []
}

But if I reverse that and set the properties firstly:

 https://address:50000/b1s/v1/Invoices?$select=CardCode,DocNum,DocDate,DocDueDate&$filter=DocDate lt '20060130' and DocDueDate gt '20060130'

I get my 2 invoices:

{
    "odata.metadata": "https://192.168.200.50:50000/b1s/v1/$metadata#Invoices",
    "value": [
        {
            "DocNum": 1,
            "DocDate": "2006-01-22",
            "DocDueDate": "2006-02-21",
            "CardCode": "C20000"
        },
        {
            "DocNum": 2,
            "DocDate": "2006-01-27",
            "DocDueDate": "2006-02-26",
            "CardCode": "C30000"
        }
    ]
}

Hope it's the reason why it didn't work for you

[former_member390407]

Hi Rahul,

You can use the following filter to get invoices between your dates (not including the filter dates):

 https://address:50000/b1s/v1/Invoices?$select=DocEntry,CardCode,DocDate&$filter=DocDate gt '20160101' and DocDate lt '20160105'

Or the following to get invoices with dates included:

 https://address:50000/b1s/v1/Invoices?$select=DocEntry,CardCode,DocDate&$filter=DocDate ge '20160101' and DocDate le '20160105'

[rahuljain257]

@SergeiTravkin thanks for replying my thread !!! It didn't worked, I have demonstrated the video explaining my issue can you please look into this !!! & suggest some solution !!! - Google Drive Link

Thanks

Rahul Jain