Incorrect time value

[norfintork]

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

[eduardosic]

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.

[norfintork]

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?

[eduardosic]

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.

[norfintork]

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

[eduardosic]

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

ok, report result's

[norfintork]

Worked perfectly, thanks again.
Scott