Calendar DATE vs database DATETIME

[jonhoward]

MSTR 7.2.3, Oracle

I have an attribute (A) with a DATETIME format in the warehouse (so format is dd/mm/yyyy hh:mm:ss)

If I build a prompt in Web I can use the calendar, but the SQL generated will have a clause along the lines of...

...where A = 31/01/1999

But the data in the warehouse will be 31/01/1999 12:34:56, therefore I get no join. In some cases though, the data may be 31/01/1999 00:00:00 in which case the join will be made

Assuming I can't make any changes to the warehouse, what is the most effective solution to this problem?

 

[bridgeconsultant]

You can use a CAST function. In Teradata, we do CAST (DateTimeColumn As Date)

 

[petperson]

In Oracle, you can use trunc(date)

 

[jonhoward]

Thanks for the pointers. I'm now using the following

ApplySimple("trunc(#0)", DATE_ATTRIBUTE)

 

[reisw]

I think you can also change the setting in the VLDB properties by defining how the Oracle date should look. You need to use an alter session statement. There's a technote on Microstrategy's support site.