Using ampscript to get all records in a data extension between 2 dates

Question

Using Marketing Cloud:

I have a data extension with a date field. On a cloudpage, using ampscript, i need to get all the records from that data extension where the date field is between/including 2 dates.

The LookupRows function seems to be the best available but i can only directly compare values to each other, it can't use a "between" statement.

What would be the best way to achieve this ? ( Other than executing a LookupRows for each date separately )

Answer 1

You will likely need to utilize SSJS via the Rows.Retrieve function.

Sample script:

var yourDE = DataExtension.Init("yourDE");
var complexfilter = {
                LeftOperand:{
                    Property:"Date",
                    SimpleOperator:"greaterThan",
                    Value: {{Start Date}}
                },
                LogicalOperator:"AND",
                RightOperand:{
                    Property:"Date",
                    SimpleOperator:"lessThan",
                    Value: {{End Date}}
        }};
var filterdata = yourDE.Rows.Retrieve(complexfilter);

Be aware though, this will only allow a maximum return of 2500 rows for each retrieve.

To get around this, you will need to do 1 of two things.

1. Utilize SOAP API to do this Retrieve Request and then use ContinueRequest property.

Sample Continue Request in SSJS:

do { 

    //your Retrieve Request API here

    rr.ContinueRequest = requestId;

} while (runstatus == "MoreDataAvailable")

2. Create a Query in SFMC and run it prior to each pull and then utilize the resulting DE as a reference for your AMPscript - I would include an attribute named 'Secret' with a value of 1 - for easy reference in your lookup.

Sample SQL:

SELECT Column1, Column2, 1 as Secret
FROM yourDE
WHERE Date >= '2017/02/25' and Date <= '2017/12/07'