3

I'm trying to save a datetime value to a MySQL database.

procedure TLocalDatabaseConnectionTests.TestSaveDateTime;
var
    StoredProc : TADOStoredProc;
    Connection : TADOConnection;
    dt : TDateTime;
begin
    StoredProc := TADOStoredProc.Create(nil);
    Connection := TADOConnection.Create(nil);
    try
        Connection.LoginPrompt := false;

        Connection.ConnectionString := String.Format
        (
            'DRIVER={%s}; SERVER=%s; DATABASE=%s; UID=%s; PASSWORD=%s;OPTION=3;',
            [
                'MySQL ODBC 3.51 Driver',
                'LOCALHOST',
                'DatabaseName',
                'Username',
                'Password'
            ]
        );

        StoredProc.Connection := Connection;
        StoredProc.ProcedureName := 'TestDate';

        StoredProc.Parameters.Clear;

        with StoredProc.Parameters.AddParameter do
        begin
            Name := String('@TheDate');
            DataType := TFieldType.ftDateTime;
            Direction := TParameterDirection.pdInput;
        end;

        dt := EncodeDateTime(1995, 12, 13, 13, 30, 1, 1);
        StoredProc.Parameters.ParamByName('@TheDate').DataType := TDataType.ftDateTime;
        StoredProc.Parameters.ParamByName('@TheDate').Value := TDateTime(dt);

        StoredProc.ExecProc;

    finally
        FreeAndNil(Connection);
        FreeAndNil(StoredProc);
    end;
end;

Here is the stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `TestDate`(TheDate DateTime)
BEGIN
    INSERT INTO new_table (idnew_table)
    VALUES (TheDate);
END

The problem is the time always gets cut off.

date

I can't explain why this is

schema

The column is indeed defined as DateTime and not Date

Ken White
  • 123,280
  • 14
  • 225
  • 444
sav
  • 2,064
  • 5
  • 25
  • 45
  • Maybe I need to upgrade my Database/connector? – sav Aug 05 '15 at 04:14
  • 2
    From the linked answer: "*You could also set the data type of the ado parameter to adDBTimeStamp yourself. **ADODB sets it to adDate when you use ftDateTime**.*" – Remy Lebeau Aug 05 '15 at 04:40
  • http://stackoverflow.com/questions/8678746/using-a-datetime-parameter-with-ado-odbc-loses-time-part indeed has the solution to this problem – sav Aug 06 '15 at 00:32

0 Answers0