OBIEE Making a distinct date prompt

One of the question I get asked on regurlar basis is: "If have a timestamp column, how do I convert this in a date only prompt".

 

One of the possible solution is this:

First Create as truncated date column in your RPD:

using :

CAST( "BM_CP_TEST"."DIM_DATE_F_FACTS"."D_DATE_RANDOM"  as DATE)

Next create a prompt:

on the show dropdown select SQL results.

Next edit the SQL to:

SELECT DISTINCT DIM_DATE_F_FACTS.DATE_TRUNC FROM BM_CP_TEST
ORDER BY DIM_DATE_F_FACTS.DATE_TRUNC

(Don't forget the order by!)

EDIT:

Till Next Time