'Date' defined by repository with timestamp information

It's been a while since the last post.

Today, I just want to talk about something we run into on our project related to defining 'date' using repository variable.

Defining a dynamic variable that returns yesterday's date is pretty easy by just adding the following statement in the initialization block:

select sysdate-1 from dual

The result shows that it also comes with hours and seconds after the date, like so:



Now changing the statement to select trunc(sysdate - 1) from dual, will remove the unwanted values at those timestamp positions following date, like so:




All these are fine and the value that the variable provides is also correct. However, we did overlook at one thing after moving forward with this setup.

Take the variable 'Yesterday_date' for instance, when we add that variable to the filter in the report, the SQL that's generated shows the following at the where clause:

T3266990.SNAPSHOTDATE = TIMESTAMP '2011-06-28 00:00:00'

Instead of having T3266990.SNAPSHOTDATE = TO_DATE('2011-06-28' , 'YYYY-MM-DD') when filtering without variable.

As far as the data is concerned, it is working correctly, however, it may affect the query's performance when it is using TIMESTAMP.

Depending on the setup of the database and it will affect the partitioning of the query if the passing parameter has timestamp in it, which isn't going to be a optimal thing to have.

The challenge now is how to use dynamic variables and still get To_Date in the SQL.

We have reached out to oracle support, basically we have been told that this is the way it is as:
"The init block returns a timestamp because OBI has no internal knowledge of what datatypes will be returned by the SQL statement. It only knows what the database tells it"

For further reading please go to:
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elements001.htm#i54335

The bottom-line is that in order to disassociate repository variable with timestamp, the datebase itself must contain date only data-type. If you are oracle DB, it automatically includes time & second in it so it will have to be changed completely, which isn't going to be easy.

Therefore the decision is up to the business as we know the factual behavior.

Looking forward to seeing whether the next release of 11G will have to way around this behavior.

Thanks

Until next time.