Date Value not getting filtered in report for equals to

Question

Hi All,

I have a issue in date value when being passed from prompts to report.

Prompt: Input type is calendar for field Effective_Frm_DT

Report: Report also has this field Effective_Frm_DT which doesn't return any data when its filtered as equal to.

Even though there is data for specific value in database, it displays as no results. eg: 09/09/016 is input value , data base has record for this value.

But when report is filtered with equal to this value it displays as no results.

Where as it works fine if i pass greater than 09/09/016 as input value, but not for 'equal to'.

Please help.

Thanks,

LonaD

986359 · Answer

This helped, now its working fine.

Thanks a lot Chris.

Chris Arnold · Answer

On the surface this seems like it might be an issue of -- even though you are only seeing the date, the column is really a timestamp.

So when you think you are comparing '09/09/016' to '09/09/016', you might actually be comparing '09/09/016 12:00:00' to something like '09/09/016 05:45:33:33'. I say this because this is certainly an issue I have faced before.

Try this --  In your prompt AND your report, refer to your date this way:

EVALUATE('trunc(%1,%2)' as Date,Effective_Frm_DT,'DD')

I hope this helps.