Today I needed to report data in Cognos8.2 Report Studio with a date range filter, based on the current day and yesterday (dynamic) and a designated (fixed) time.
Values had to be extracted with a DateTime between yesterday 9 AM and today 9 AM. As we use an Oracle database it should be easy like:
[DateTime] >= TO_TIMESTAMP (to_char({sysdate}-1) || ' 09:00:00.000000', 'DD-Mon-RR HH24:MI:SS.FF')
AND
[DateTime] < TO_TIMESTAMP (to_char({sysdate}) || ' 09:00:00.000000', 'DD-Mon-RR HH24:MI:SS.FF')
However, on this particular reporting environment, we are not permitted to use native functions. Hence, I wrote the Cognos8 syntax (in the Detail Filter expression):
[DateTime]
-
_make_timestamp
(
extract(year,(_add_days(current_date,-1) ) )
, extract(month, (_add_days(current_date,-1) ) )
, extract(day, (_add_days(current_date,-1) ) )
)
>=9 hour
AND
[DateTime]
-
_make_timestamp
(
extract(year,current_date )
, extract(month,current_date )
, extract(day,current_date )
)
<9 hour
