Tuesday, February 25, 2014

DateTime field conversion to a Date field in OBIEE

If you have a field in physical table which is of type DateTime, but you want to display it as only date in the report, then you this can be converted. There are two approaches to it in the RPD:

 1. In the physical layer, navigate to the physical table and double click the datetime column. Next change the datatype from 'DATETIME' to 'DATE'.


This will make only cosmetic change, i.e. anytime the field is exposed in answers, it will not display the time, but for calculations (or joins), it will use the database value of date time.

2. A better way might be to use cast function to convert the field to date in the logical layer expression builder as below:

Adding cast function to this field will append TRUNC function in the physical query fired to the database. Had to add this post, in the expression builder, there is no truncate function in "Calendar Date/Time Functions". While if you look documentation, for cast, it clearly states that it supports DateTime as well as Date data types:


  1. Typo in your screenshot for #2.. says CASE instead of CAST

    1. Oops! Fixed it now. Thanks for letting me know.