DB Agnostic Dates

74 Views

I'm trying to create a Universe that can use connections to (just about) any database. Specifically DB2, SQL Server and Oracle for the time being.

Everything works fine with the exception of dates to be used in report prompts. DB2 dates used in prompts fail for lack of the time elements in the calendar widget.

To get them all to midnight (or non-timestamped) date:

A DB2 timestamp field has to be defined as Date([field])

An Oracle datetime needs to be trunc([field])

SQL Server need to be DateAdd(dd,DateDiff(dd,0,[field]),0)

But, of course, I can't have all 3 of those definitions in a single object, I don't think...

Do I just have to reference the field and format it in the Universe to the longest format? Or do I need to hack some config file? Can I add functions to the .prm files for each database flavor with the same names so that applying a function is applying the logic I need for each?

Searches don't turn up this specific issue.