Filter Gateway Date with SAP CAP CQL

[jmtrujillogalan]

Hi everyone,

I'm trying to filter Date in Gateway system using SAP CAP CQL notations with Nodejs.

https://cap.cloud.sap/docs/node.js/cds-ql#where

When i built my where condition like this:

SELECT.from(X).where({ Date: { between: min, and: max }})

The URL generated was: (...Date gt datetime'xxxx' and Date lt datetime'xxxx ) but the standard filter in gateway (io_tech_request_context->get_filter) doesn't support these operators. It's neccesary to use 'le' and 'ge'.

Is there any solution?

Thanks and Regards

[jmtrujillogalan · Accepted solution]

Finally I declined to use filter with objects and I created the dinamyc where condition with string interpolation.

queryWhere = addWhereCondition(queryWhere, 'DeliveryCode', aDeliveries)
queryWhere = addWhereCondition(queryWhere, 'ActualGI', req.data.ActualGIFrom, req.data.ActualGITo)

function addWhereCondition(sWhere, sProperty, aParam, aParamTo) {

    if (!aParamTo) {
        if (sWhere == ``) {
            if (aParam.length == 1) {
                sWhere = ` ${sProperty} = ${aParam}  `;
            } else {
                sWhere = ` ${sProperty} in (${aParam})  `;
            }
        } else {
            if (aParam.length == 1) {
                sWhere = ` ${sWhere} and  ${sProperty} = ${aParam}  `;
            } else {
                sWhere = ` ${sWhere} and  ${sProperty} in (${aParam})  `;
            }
        }
    } else {
        if (sWhere == ``) {
            sWhere = ` ( ${sProperty} >= ${aParam} and ${sProperty} <= ${aParamTo} ) `;
        } else {
            sWhere = ` ${sWhere} and ( ${sProperty} >= ${aParam} and ${sProperty} <= ${aParamTo}) `;
        }
    }
    return sWhere;
}

I hope it helps you!

Regards