6 Replies Latest reply on Nov 1, 2017 12:37 PM by gmichaud-verreault

    Conversion failed when converting date and/or time from character string - AF Query

    Maitreyi

      Hi,

       

      We have a query built in our AF. The definition of the table is as follows:

       

      pisquare pic.png

       

      Unfortunately the event frames constantly spits out reams of the following message:

       

      Function System.String FillDataSetCommon(OSIsoft.AF.Service.CallContext, System.String, System.String, System.String, Boolean, Byte[], OSIsoft.AF.Service.dcTableColumnDef[], OSIsoft.AF.Service.dcTableParameter[], System.Collections.Generic.Dictionary`2[System.String,System.String], OSIsoft.AF.Service.dcServerError ByRef) at line: 0 in file

       

      System.Data.OleDb.OleDbException (0x80040E07): Conversion failed when converting date and/or time from character string.

         at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)

         at System.Data.OleDb.OleDbDataReader.NextResult()

         at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

         at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)

         at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

         at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

         at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

         at OSIsoft.AF.Service.AFService.FillDataSetCommon(CallContext callContext, String dataAdapterType, String connection, String command, Boolean impersonateUser, Byte[] encryptedPassword, dcTableColumnDef[] tableColumnDefs, dcTableParameter[] parameters, Dictionary`2 defaultParameters, dcServerError& err)

       

      dcServerError state:

      Operation =  0

      ErrorNumber =  -2

      Message =  Conversion failed when converting date and/or time from character string.

      Procedure =  Microsoft SQL Server Native Client 11.0

      ObjectType =  43

      ID =  b6c74dd6-6ce8-47a5-929a-9d2966565bee

      ObjectName =  MAIQuery

      SubObjectType =  0

      User =  Unknown

      LockedByUser =     at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)

         at System.Data.OleDb.OleDbDataReader.NextResult()

         at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

         at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)

         at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

         at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

         at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

         at OSIsoft.AF.Service.AFService.FillDataSetCommon(CallContext callContext, String dataAdapterType, String connection, String command, Boolean impersonateUser, Byte[] encryptedPassword, dcTableColumnDef[] tableColumnDefs, dcTableParameter[] parameters, Dictionary`2 defaultParameters, dcServerError& err)

      OtherObjectType =  0

      OtherObjectName =  Unknown

      OtherObjectID =  00000000-0000-0000-0000-000000000000

       

       

      Associated Query:

       

      select Monitor_Point_ID, Determinand_Code, DATE_TIME_TAK_TMSP, DETERMINND_VAL_NUM, NORM_UNIT_CODE

      from XTable

      where MONITOR_POINT_ID=@MPI and DATE_TIME_TAK_TMSP >= @QueryStartDate

      Order by DATE_TIME_TAK_TMSP

       

      The data type of the field DATE_TIME_TAK_TMSP in the XTable is again DateTime.

       

      Any idea why?

       

      (Attachment is a Word docx file containing this post.)