We are able to connect to BIGQUERY and retrieve data fine however have noticed several functions don't appear to work properly. They seem to primarily revolve around date/time manipulation. I believe the business date time function _add_days() should be getting translated into a BIGQUERY equivalent of date_add() but instead we are getting function not found for _add_days(). If we try to use the vendor function date_add() we get an error when specifying INTERVAL 5 DAYS. I think it is probably pulling a vendor function from another database and I will need to educate the developers not to include all the database vendors to avoid that. They did test using the date_add() function on pass through SQL and that works but has some limitations.

------------------------------
Robert Hofstetter
------------------------------

#CognosAnalyticswithWatson

Suggest you open a support ticket.

_add_days will use GBQ DATE_ADD when the input type is a DATE or TIMESTAMP with TZ

ensure that you aren't trying to pass an input value such as STRING or an unknown type value as the first parameter.

------------------------------
NIGEL CAMPBELL
------------------------------

Here is a link to Google documentation related to this topic:  https://cloud.google.com/bigquery/docs/reference/standard-sql/operators?hl=en_US#date_arithmetics_operators

We are recommending as we transition to GCP BigQuery that cognos developers will need to rewrite anything using date, datetime, time and timestamp functions.

------------------------------
Kay Vandevanter
------------------------------

Should you be migrating from another vendor, there may be constructs where GBQ differs.

For example,  GBQ DATE is a DATE (as it is in many vendors, except say ORACLE/Exasol where it is really a timestamp).

Meanwhile GBQ TIMESTAMP is effectively a TIMESTAMP with TZ where values are stored at UTC, as some other vendors do.

While  GBQ DATETIME is not stored at UTC.

Many of the operations to _add, _x_between etc, will be generated using the equivalent GBQ expressions where DATE, TIME and TIMESTAMP TZ are involved.

If you have further concerns, please contact support.

Generally, would recommend that you be using CA 11.1.4+, as it automatically generates SQL to compensate for GBQ limitations, which in several cases Google have since improved in their updates.

Similarly, be aware of the Google BigQuery JDBC driver, for example http://www.ibm.com/support/pages/node/6350937

Ideally, you would be using more current Google drivers, but should you be using older versions avoid  http://www.ibm.com/support/pages/node/6350933

​​

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Wed April 28, 2021 12:45 PM
From: Kay Vandevanter
Subject: Google BIGQUERY function not found: _ADD_DAYS

Here is a link to Google documentation related to this topic:  https://cloud.google.com/bigquery/docs/reference/standard-sql/operators?hl=en_US#date_arithmetics_operators

We are recommending as we transition to GCP BigQuery that cognos developers will need to rewrite anything using date, datetime, time and timestamp functions.

------------------------------
Kay Vandevanter
------------------------------