How to define datepart Input variable in DATEADD (datepart , number , date ) method, so that it can be used as Source Supported Functions

[ragava28]

Hi All,

I am trying to define a Source supported function (DATEADD) for SQL server data source in VDB (Created using Teiid Designer).  I am following below url for same.

How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source

Source Supported Functions - Teiid 9.0 (draft) - Project Documentation Editor

Definition of method which I am trying to define :  DATEADD (datepart , number , date ) . AS per MSDN documentation DATEADD (Transact-SQL)

"datepart

Is the part of date to which an integernumber is added. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

number

Is an expression that can be resolved to an int that is added to a datepart of date. User-defined variables are valid.

date

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal.

"

As per msdn there are no User-defined variable equivalents for datepart.  What should be the data type of this input argument in VDB ?

I tried with string but no luck.

below is the error log. Attached the complete log

2016-06-28 15:17:33,465 FINE  [com.microsoft.sqlserver.jdbc.internals.SQLServerStatement] (Worker378_QueryProcessorQueue36201) SQLServerPreparedStatement:2338: calling sp_prepexec: PreparedHandle:0, SQL:SELECT TOP 100 DATEADD('DAY', -1, CAST('2016-06-28' AS DATE)) AS c_0 FROM "DSProdDB"."dbo"."IPSO_ETL_MASTER" g_0

2016-06-28 15:17:33,466 FINE  [com.microsoft.sqlserver.jdbc.internals.SQLServerException] (Worker378_QueryProcessorQueue36201) *** SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid parameter 1 specified for dateadd. Msg 1023, Level 15, State 1, Invalid parameter 1 specified for dateadd.

Any thoughts/alternatives on this would be really helpful.

[shawkins]

The major issue here is that datepart is expected to be a keyword - but the default rendering of the sql will produce a literal.  This adds some difficultly in mapping to a user defined function.

SQLServer translator already maps the timestampadd system function to the SQLServer dateadd function and handles the source sql for you.  Is there a reason you don't want to use the system timestampadd function?

[ragava28]

Steven ,

Thanks for response . You are right. Even I realized this after posting it in forum. Initially I tried executing timestampadd  in SQL server directly, which was not working.so thought it is not supported by sql server. So ended up exploring other options which took me to DateAdd & pushing same to source.

Sorry for confusion.

Regards

Raghu