Devart Forums

Incorrect time value: '10:41:03 AM' for column 'endtime' at row 1.


Having an issue with an update query. Using a table method worked fine in the past.



*** This works fine
Datamodule1.MyCM.edit;
Datamodule1.MyCM.fieldbyname('status').value:= 'Complete';
Datamodule1.MyCM.fieldbyname('endtime').value:= timetostr(now);
Datamodule1.MyCM.post;

*** This works fine
Datamodule1.MyCMQ.sql.Clear;
Datamodule1.MyCMQ.sql.add('UPDATE casemaster SET status = "Complete"');
Datamodule1.MyCMQ.sql.add(' where autoid =' + autoid.text);
Datamodule1.MyCMQ.Execute;

*** This fails with:
Incorrect time value: '10:41:03 AM' for column 'endtime' at row 1.

Datamodule1.MyCMQ.sql.Clear;
Datamodule1.MyCMQ.sql.add('UPDATE casemaster SET endtime = '+ quotedstr(timetostr(now)));
Datamodule1.MyCMQ.sql.add(' where autoid =' + autoid.text);
Datamodule1.MyCMQ.Execute;



MySQL data type is TIME

MyDac 3.55.0.26 for D7

MSQL 5.0.45

Thanks !
Scott

norfintork wrote:Incorrect time value: '10:41:03 AM' for column 'endtime' at row 1.


Having an issue with an update query. Using a table method worked fine in the past.



*** This works fine
Datamodule1.MyCM.edit;
Datamodule1.MyCM.fieldbyname('status').value:= 'Complete';
Datamodule1.MyCM.fieldbyname('endtime').value:= timetostr(now);
Datamodule1.MyCM.post;

*** This works fine
Datamodule1.MyCMQ.sql.Clear;
Datamodule1.MyCMQ.sql.add('UPDATE casemaster SET status = "Complete"');
Datamodule1.MyCMQ.sql.add(' where autoid =' + autoid.text);
Datamodule1.MyCMQ.Execute;

*** This fails with:
Incorrect time value: '10:41:03 AM' for column 'endtime' at row 1.

Datamodule1.MyCMQ.sql.Clear;
Datamodule1.MyCMQ.sql.add('UPDATE casemaster SET endtime = '+ quotedstr(timetostr(now)));
Datamodule1.MyCMQ.sql.add(' where autoid =' + autoid.text);
Datamodule1.MyCMQ.Execute;



MySQL data type is TIME

MyDac 3.55.0.26 for D7

MSQL 5.0.45

Thanks !
Scott

See the time format in Control Panel of Windows..

or in the application set the time format in application.

The Windows time format checks out. Using the table method (which works) vs. the query, the time is stored as 14:14:14. What is differnet about the way quotedstr(timetostr(now))) is stored with a table write vs. a query write?

norfintork wrote:The Windows time format checks out. Using the table method (which works) vs. the query, the time is stored as 14:14:14. What is differnet about the way quotedstr(timetostr(now))) is stored with a table write vs. a query write?

use parameters:

with Datamodule1.MyCMQ do begin
Sql.Clear;
Sql.add('UPDATE casemaster SET endtime = :end_Time'
Sql.add(' where autoid =' + autoid.text);
ParamByName( 'end_Time' ).AsDateTime := now;
Execute;
end;

parameters convert the time for a correct format.

Thanks very much - I will give that a try.
Scott

norfintork wrote:Thanks very much - I will give that a try.
Scott

ok, report result's

Worked perfectly, thanks again.
Scott