Filtering Issue

[Prerna Chaudhary]

Hi,

I'm facing one blocker issue.

The start and end date/time that has been calculated explicitly to filter the data from database, is showing correct value when simply put on the report. Please refer the attached snapshot for the same.

However, the same value is not working when put as a filter. I get no data for the date range that I have calculated using cognos operators. The condition being used is exactly the same as being used in the above mentioned case.

if I take a prompt value from user in a date time prompt as ?start? and ?end? then the below filter works fine:

For start time:  [abc].[START_TIME] >= ?start?

For end time:   [abc].[END_TIME] <= ?end?

However, when I calculate the current date/time value using the cognos operators/functions and use that to filter then the same doesnt work:

For start time:  [abc].[START_TIME] >= _make_timestamp (2014,12,01)

For end time:   [abc].[END_TIME] <= _make_timestamp (2014,12,15)


To get a better idea of the filters that I wrote, I created another query with the working filters and tried to check whether my calculated date is comparable to the DB date or not.
The query had following data items:

start_calc
-----------
_make_timestamp (2014,12,08)

START_TIME
-----------
[abc].[START_TIME]

startBool
----------
IF ([abc].[START_TIME] >= _make_timestamp (2014,12,08)) THEN
    ('TRUE')
ELSE
    ( 'FALSE' )
   
end_calc
-----------
_make_timestamp (2014,12,15)

END_TIME
-----------
[abc].[END_TIME]

EndBool
-----------
IF ( [abc].[END_TIME] <= _make_timestamp (2014,12,15)) THEN
    ('TRUE')
ELSE
    ( 'FALSE' )

The above query results into the attched snapshot.


Could you please suggest something. Is it some kind of limitation in cognos?


Thanks and regards
Prerna

[BigChris]

I can't see anything obvious wrong in your calculations etc. Is there any chance that you're getting issues with the time elements? It looks like you're START_TIME and END_TIME fields are all at midnight...and that the _make_timestamp fields are all at the current time...