2 Replies Latest reply on Jun 28, 2016 9:16 PM by Raghu Kasukurthi

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

    Raghu Kasukurthi Newbie

      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.

      q1.png

      q2.png

      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.