what is the data type of DATE and TIME please? I believe this error is caused by data tpye cannot be identified. I think you could try to cast the DATE and TIME to string type, and link them together. And then, put this string type into PI_TIMESTAMP column.
Tq Xi Wang,
Tech support has reply the above matter
From the user manual,
dBase III, dBase IV
Date and Time Data Type
dBase does not have any native timestamp data type. If sending PI timestamps to dBase, the interface and the ODBC driver will automatically convert the timestamp placeholder from the SQL_TIMESTAMP into SQL_VARCHAR (the dBase target column therefore has to be TEXT(20)).
The other direction RDB->PI is not that simple. Actually, it is not possible to read a timestamp from a TEXT field because the required ODBC function CONVERT does not support the SQL_VARCHAR into SQL_TIMESTAMP conversion either. However, a workaround is possible:
Use the dBase database as a linked table from within MS Access. Now the MS Access ODBC driver is available, which implements a function called CDATE(). The following query works for string columns e.g. TEXT(20) in dBase with the format "DD-MMM-YY hh:mm:ss":
SELECT CDATE(Timestamp), Value, Status FROM Table WHERE CDATE(Timestamp) > ?; P1=TS
ODBC drivers used:
Microsoft dBase Driver 4.00.4403.02
Microsoft Access Driver 4.00.4403.02
This may be the issue you are facing. You may want to try the work around.
By using the CDATE function at MS Access then it work.. Only the date format is quite confusing, on D/M/Y or M/D/Y.
If in my database column store date day less than 12 (cause we only have 12month),
i:e 01/02/2013 - PI assume it 02-Jan-2013 (it wrong) however if the day at the date is over 12 i.e: 13/02/2013 - PI assume it as 13-Feb-2013 which is right.
I have try to use format(DATE,'YYYY-MMM-DD') function at the SQL, but it still goes to the same result for 01/02/2013.
Yes, PI time is dd-mmm-yyyy. That is the reason why I suggested convert your source data type from dBase to string. If the type is string, you could seperate the day, month and year into different columns with the string data type, and could conbine these columns into one column with the different format order. This is the SQL language supported, will spend you some time to do.
Tq Xi Wang, it now fixed :)
i'm using this SQL statement to handle storing data in D/M/Y format
SELECT CDATE(MID(DATE,4,2) + '/' + MID(DATE,1,2) + '/' + MID(DATE,7,4) + ' ' + TIME) as PI_TIMESTAMP,VAR_STATE as PI_VALUE FROM JOURNAL
However if it already in M/D/Y format
SELECT CDATE(DATE + ' ' + TIME) as PI_TIMESTAMP,VAR_STATE as PI_VALUE FROM JOURNAL