Truncating the Time by Creating a Derived Column

In the IBM® Cognos® Framework Manager model, create a derived column using a calculated expression that truncates the time from a timestamp, and returns a timestamp containing the original date and a default time. For example, if you use an Oracle data source, the following expression creates the derived column DATEONLY from COL1, where COL1 contains the values as stored by the application while DATEONLY contains the dates with the default time of 12:00:00 AM:

Select COL1,trunc(COL1) as DATEONLY from [SCOTT_TIGER].DATES

Tip: You can change the data format of the column to show only the date value by setting the Date Style property to Short.

You can then apply filters to the DATEONLY column that would return the correct results. If you create a parameter in IBM Cognos Analytics - Reporting that filters on this column, the default prompt will present a date and time control because the data type is still a timestamp.