CAST Date in Filter and use Pre-defined Periods

Amulya Sharma shared this question 2 years ago
Answered

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.

Comments (2)

photo
1

Hi Amulya,


I think that I get what you are going for here, but would you be interested in scheduling a time to screen-share? If so, please let me know what time works best for you. I am on US Mountain Time.


Regards,

Nathan

photo
1

Hi Amulya,


Have you had any luck in resolving this? I am going to set this ticket to closed for now, but if there is anything I can do to help here, please just let me know and the case will be re-opened!


Regards,

Nathan