Dashboardpromt: OBIEE automatically converts Year (VARCHAR) into Datetime.
789348Aug 30 2011 — edited Aug 30 2011Hi everyone,
today I have a question on date-formats and dashboardprompts:
I have a physical table, which is used for creating a time dimension/hierarchy, which, i think works well.
The Problem I have is, that I theres a column, where the years are presented as text (*Varchar*). Id like to use this column in a dashboardprompt, to let people filter the reports on a certain date. So the content of this column is 2005, 2006, 2007, 2008 and so on.
When I try to constrain the Date on the year, chosen by the user, in the dashboardprompt, obiee fails to get results.
I had a look on the physical query, which is sent to the bi server:
SELECT Time.DATE FROM subjectarea WHERE TIME.YEAR_TEXT = '2011-null-null 00:00:00' ORDER BY 1
I thought it should have been the following query: SELECT Time.DATE FROM subjectarea WHERE TIME.YEAR_TEXT = '2011' ORDER BY 1
Q: Why does OBIEE convert '2011' to '2011-null-null 00:00:00' on its own? As I already mentioned: The datatype of the year_text column is VARCHAR within the database and ofcourse in phys./business model.
Is there an option to prevent obiee, from auto-converting years into datetimes?
Maybe someone of you already had to fight the same issue and knows a solution.
Thanks in advance,
Chris.