AnsweredAssumed Answered

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

Question asked by Maitreyi on Oct 27, 2017
Latest reply on Nov 1, 2017 by gmichaud-verreault

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.)

Attachments

Outcomes