1733811 - Teradata Date Calculations error IDT

 A Multi-source UNX universe errors with time, date or timestamp formats. 

Data Federator Query server (and thus multi-source universes) do not support (+, -)  operations on types time/date/timestamp.

The following scalar functions are supported (based on JDBC specifications) :

-        TIMESTAMP timestampadd(INTEGER interval, INTEGER count, TIMESTAMP timestamp)

TIMESTAMP timestampadd(STRING  interval, INTEGER count, TIMESTAMP timestamp)

Returns a timestamp calculated by adding count numbers of interval(s) to timestamp. Interval may be one of the following value

SQL_TSI_FRAC_SECOND = 0

SQL_TSI_SECOND = 1

SQL_TSI_MINUTE = 2

SQL_TSI_HOUR = 3

SQL_TSI_DAY = 4

SQL_TSI_WEEK = 5

SQL_TSI_MONTH = 6

SQL_TSI_QUARTER = 7

SQL_TSI_YEAR = 8

-        TIMESTAMP timestampdiff(INTEGER interval, TIMESTAMP timestamp1, TIMESTAMP timestamp2)

TIMESTAMP timestampdiff(STRING  interval, TIMESTAMP timestamp1, TIMESTAMP timestamp2)

Returns an integer representing the number of intervals by which timestamp2 is greater than timestamp1. Interval may be one of the following value :

SQL_TSI_FRAC_SECOND = 0

SQL_TSI_SECOND = 1

SQL_TSI_MINUTE = 2

SQL_TSI_HOUR = 3

SQL_TSI_DAY = 4

SQL_TSI_WEEK = 5

SQL_TSI_MONTH = 6

SQL_TSI_QUARTER = 7

SQL_TSI_YEAR = 8

Examples:

SELECT  timestampdiff('SQL_TSI_WEEK',  toTimestamp('2012-01-01 0:0:0'), now())   FROM MyTable

SELECT  timestampdiff(6,  toTimestamp('2012-01-01 0:0:0'), now())   FROM MyTable

Below is a working example of calculated column (not database specific) using such functions

• SAP BusinessObjects Business Intelligence platform 4.x
• Information Design Tool (IDT) UNX Multi-source
• Web Intelligence (Webi)
• Data Federation Administration Tool (DFAT)

KBA , BI-BIP-SL , Semantic Layer , Problem