Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: Magdalina08 on 23 Oct 2019 02:17:08 PM

Title: Use Microseconds for Date Prompt - updated w/ better req
Post by: Magdalina08 on 23 Oct 2019 02:17:08 PM
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.
Title: Re: Use Microseconds for Date Prompt - updated w/ better req
Post by: Francis aka khayman on 24 Oct 2019 01:18:53 PM
get the month, day and year from the date and filter on those
Title: Re: Use Microseconds for Date Prompt - updated w/ better req
Post by: Magdalina08 on 24 Oct 2019 01:22:13 PM
DB2 can't use an index if you cast the value.  So we need to use the entire TS for the prompt.
Title: Re: Use Microseconds for Date Prompt - updated w/ better req
Post by: Francis aka khayman on 24 Oct 2019 04:33:02 PM
sorry i do not understand what an index got to do with the report
Title: Re: Use Microseconds for Date Prompt - updated w/ better req
Post by: Magdalina08 on 24 Oct 2019 04:36:33 PM
Quote from: Francis aka khayman on 24 Oct 2019 04:33:02 PM
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


Title: Re: Use Microseconds for Date Prompt - updated w/ better req
Post by: Francis aka khayman on 24 Oct 2019 04:47:49 PM
you are talking about a prompt in Framework Manager? not in Report Studio?
Title: Re: Use Microseconds for Date Prompt - updated w/ better req
Post by: Magdalina08 on 24 Oct 2019 04:49:13 PM
Date prompt in Report Studio, but as I was researching I saw that precision could be adjusted in Framework.  Only to milliseconds though.
Title: Re: Use Microseconds for Date Prompt - updated w/ better req
Post by: Francis aka khayman on 24 Oct 2019 04:51:34 PM
ok in report studio, can you not extract the month and year from a date? and use that to filter?
Title: Re: Use Microseconds for Date Prompt - updated w/ better req
Post by: Magdalina08 on 24 Oct 2019 05:01:41 PM
No, because that would involve a CAST.  He says he can't Index a CAST in DB2.  That is my current setup.
Title: Re: Use Microseconds for Date Prompt - updated w/ better req
Post by: Magdalina08 on 24 Oct 2019 05:04:54 PM
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
Text only | Text with Images