CAST Date in Filter and use Pre-defined Periods
My aim is to remove the timestamp off the datetime field and use it as a Filter. When I use the in-built functions (Date calculation at View level, Truncate timestamp function at Report level etc.), it does a funky CONVERT calculation:
convert(datetime, convert(varchar(20), datetimefield, 112), 112) BETWEEN '20170501 00:00:00.000' AND '20170531 23:59:59.000')
Not only does it not effectively remove the timestamp off the field (results still get impacted by time selections on filters), it also still gives the option to select the actual time at the "Output" to the User when all the User wants to do is deal strictly with Dates and not factor in time.
CASTING would work to remove timestamp and its effect completely. A calculated field containing CAST(datetimefield AS DATE), would work but I cannot add this calculated field to the filter list and I cannot use Pre-defined Periods like "Last Calendar Month" with this.
How do I effectively remove the timestamp through CASTING while also being able to use Predefined Periods? Is this possible?
P.S: I cannot use Pre-defined periods in filters while using Freehand SQL reports and hence I had to try and use Drag&Drop builder for this in the first place. Thanks.