Both these values give me the date in this format:
mm/dd/yy hh/mm/ss; though I set the Object format to dd/mm/yy.
So what happens is I’m not able to equate these objects as they do not match up at the time level, even if the dates are same. I’m only concerend with the dates.
For Example, if STARTDATE = 06/25/04 11:59:34 am; and
CURRDATE = 06/25/04 10:33:46 am, then the query doesnt return any value. But I want the start date to be returned.
Your best bet:
Ask your universe designer to provide a Current Date universe object with no or zero timestamp (truncated) and ask him/her to provide a Start Date object without a timestamp (truncated).
When you change the object format to dd/mm/yy, I presume your doing this in the report, it is done after the 2 dates are compared. The format of the 2 objects your comparing needs to change (whether to mm/dd/yy or dd/mm/yy) before the sql statement that is comparing them. Therefore, it needs to change at the Universe level. Another suggestion, other than trunc(), is to change the objects at the universe level by using another function, to_char(,‘dd/mm/yy’), so the object will already be formatted the way you want it in the report without formatting it in the report. (keep in mind it is changing the datatype of the object to character). IF you want the datatype to stay as date, then try to_date(to_char(,‘dd/mm/yy’)).