your syntax is a mixture of dates and timestamps, this will not work, you need to be consistent.
Always explicitly cast rather than relying on implicit conversions, implicit will 'bite you'.
So where you have dates currently cast them to timestamp instead, you will have to have the right default formats 00:00:00 and you may have to concatenate similar midnight times on the end of your date field.
The number of times I wish OBIEE had to_date to_timestamp the same as Oracle DB (I know you can use evaluate, but it is not as performant) as converting dates / timestamps is invariably painful...
Well ... it does have it:
Okay, this had passed me by, a recent addition?
And is there a To_Date also?
I suspect this will also be a godsend to user3507711 in simplifying the syntax (and actually making it work which is always a bonus...)
To_DateTime is an RPD conversion as well as available (and working) in Answers since...at least 22.214.171.124.
It does NOT get rendered in the formula editor in Answer though. Still works perfectly.
No To_Date though but you can just format it down to a date.
Heh no not at all. No clue why this still ahsn't been fixed. Seems like a supremely small thing to implement if the functionality is there and works.
user3507711 if the whole conversation is tl;dr for you:
Bug 26428839 exists to have it in the GUI as well, but...nothing
Edit: it actually goes back to before 126.96.36.199
Thanks a lot.
It worked for me.
Thank you everyone for all your inputs.