Passing Date Range to Lobby Page Parameter

Lobby Datasource Where condition will be as follows, to create Parameters:

created_date between '$starting_date$' and '$end_date$'

Obviously this needs to be slightly amended based on whether you want the lobby to return any values if the user does NOT populate page parameters and leave them blank, such as this:

created_date between to_date(nvl('$starting_date$','01/01/1999'),'dd/mm/yyyy') and to_date(nvl('$end_date$','31/12/2099'),'dd/mm/yyyy')

Also depends on whether you want to enforce a date format for the parameter or let the lobby handle it on its own, typically if you leave the parameters as dates, the format is likely going to be yyyy-mm-dd

example:

Also note that best practice if the user is allowed to leave values blank is to add a blank condition check so that if both are blank the query plan will not need to execute the date comparison on all records to figure out if they match or not. This is important if your dataset has a large number of rows.

You can achieve that in many ways, here’s the one I typically use:

((nvl('$starting_date$','%') = '%' and nvl('$end_date$','%') = '%') or date_entered between to_date(nvl('$starting_date$','01/01/1999'),'dd/mm/yyyy') and to_date(nvl('$end_date$','31/12/2099'),'dd/mm/yyyy'))

Where the first bit of that where condition, “(nvl('$starting_date$','%') = '%' and nvl('$end_date$','%') = '%')” will automatically evaluate to TRUE if both parameters are blank, and therefore the Query Plan does not need to actually do any date comparison on all records just to see if they match the “OR” condition there. Helps speed us things significantly on large datasets ESPECIALLY if doing evaluations/comparisons on non indexed fields.