Calculate difference between two dates connecting to a BW cube via JCo

[Former Member]

Hello,

This sounds simple but I haven't been able to get it to work. I have a start date composed of a date field and a time field (I should probably merge the two into a DateTime field) and an end date composed of only a date field. I need to calculate the difference between the two in days (preferably hours as well). Using timestampdiff() fails as follows:

timestampdiff(4, cast(@Select(Date type field) as timestamp), cast(@Select(Date type field) as timestamp))

Returns incorrect values. For example, with two dates of 5/2/2013 and 5/15/2013, I get a result of -26.

timestampdiff(4, @Select(Date type field), @Select(Date type field))

When I click Validate in IDT, it says, "Unsupported signature 'TIMESTAMPDIFF(INTEGER, DATE, DATE)'"

We are connecting to a BW cube with a JCo relational connection and the underlying BW database is SQL Server. I tried using the native datediff() function but it failed with "The column name 'd' could not be found in any of the tables or subqueries of the FROM clause."

Any ideas?

Thanks.

[Former Member]

Datediff() function should work but have you made sure the start date and end date are store as Date data type and not datetime

[Former Member]

Durgamadhab,

Yes, they are both Date fields. But I found that note 1905202 says, "Multisource-enabled data foundations only support SQL-92 standard syntax and SAP BusinessObjects SQL functions, database specific syntax is not supported." So that means I can't use datediff() or any other SQL Server function. Now I'm trying to find a list of ANSI SQL-92 date functions but apparently the ANSI standard only defines syntax, not functions.

Do you know what I am doing wrong with timestampdiff()?

[Former Member]

I replaced the @Select() statements with @catalog() pointing directly to the fields in the tables, but I still get "Unsupported signature 'TIMESTAMPDIFF(INTEGER, DATE, DATE)'".

[Former Member]

Well you did not mentioned you have a multi source universe in your question.. For a multi source universe you can not have a database specific syntax as it needs to be in SQL-92 which can be parsed by the data federation engine..

Try with the below syntax.. which is SQL-92

EXTRACT(DAY FROM DATE(table.start Date) - DATE(table.End Date))

For timestampdiff function try with the below syntax

TIMESTAMPDIFF(SQL_TSI_DAY, DATE, DATE)