Use Microseconds for Date Prompt - updated w/ better req

[Magdalina08]

We use DB2 and we have a date prompt.  The SQL generated for the ending TS searches for: 23.59.59.999000.  Is there a way to display (query) the microseconds as well?  I saw you can change formatting in Framework, but I did not see anything for microseconds and there was nothing about that covered for patterns.

Another possibility we have discussed is that the date prompt that does not generate DATE or TIMESTAMP when being passed to the DB.  Is there a way to do that?  I tried substing([DATE], 1, 11) || '24.00.00.000000' , but this did not work either.

** I want to query all records for a single day.

The date prompt is currently generating an end timestamp with millisecond precision - 23:59.59.999000

This will miss any record whose timestamp is in the last millisecond of the day.

Is there a way that Cognos can generate the end timestamp with microsecond precision - 23:59.59.999999?

So basically, can Cognos send a WHERE clause to DB2 with a timestamp that covers the full day and doesn't leave out the last millisecond of the day?  It would be ideal to be without a TIMESTAMP function.

[Francis aka khayman]

get the month, day and year from the date and filter on those

[Magdalina08]

DB2 can't use an index if you cast the value.  So we need to use the entire TS for the prompt.

[Francis aka khayman]

sorry i do not understand what an index got to do with the report

[Magdalina08]

sorry i do not understand what an index got to do with the report

Our DBA is putting indexes on all prompt values to improve DB efficiency for searches.  He wants to put one on the values used for the date prompts as well.  Now here I am.  :o

[Francis aka khayman]

you are talking about a prompt in Framework Manager? not in Report Studio?

[Magdalina08]

Date prompt in Report Studio, but as I was researching I saw that precision could be adjusted in Framework.  Only to milliseconds though.

[Francis aka khayman]

ok in report studio, can you not extract the month and year from a date? and use that to filter?

[Magdalina08]

No, because that would involve a CAST.  He says he can't Index a CAST in DB2.  That is my current setup.

[Magdalina08]

My thought is that, if I use [DATE_TS] then Cast to [DATE] in Cognos, he should still be able to index the [DATE_TS] because logically that is what it would be searching for.....does that not seem correct?

I don't think this is correct...it would only search for the casted value