CODEWITHSUNDEEP
androidmysqljdbc
iabbott
iabbott

Reputation: 881

Data truncation-Incorrect date value when trying to insert a time

My Insert statement

INSERT INTO tblData_FY14 
VALUES (STR_TO_DATE('24/07/2013','%d/%m/%Y'), 
        STR_TO_DATE('14:31:07','%h:%i:%s'), 
        STR_TO_DATE('14:31:20','%h:%i:%s'), 
        1, 1, 2, 3, 4, 13)

The error I get:

com.mysql.jdbc.MysqlDataTruncation: Data truncation:
         Incorrect date value: '14:33:21' for column 'TimeofStop' at row 1

I can't work out why I'm getting the error, as far as I can see the format is correct...

I had the same issue with the date before this, but managed to solve it googling for the correct format, but my efforts for this time issue have come to nothing so far

EDIT see the C# code for creating this table:

MySqlCommand command = new MySqlCommand("CREATE TABLE IF NOT EXISTS tblData" + GVars.curFY +
                        " (" +
                        "DateofStop DATE, " +
                        "TimeofStop DATETIME, " +
                        "TimeofStart DATETIME, " +
                        "CategoryID INT REFERENCES tblDtCategory(CategoryID), " +
                        "DowntimeID INT REFERENCES tblDowntime(DowntimeID), " +
                        "AreaID INT REFERENCES tblMachineArea(AreaID), " +
                        "ProblemID INT REFERENCES tblProblem(ProblemID), " +
                        "CauseID INT REFERENCES tblCause(CauseID), " +
                        "DowntimeSeconds INT);", dbConn);

The type of "TimeofStop" and "TimeofStart" are datetime However, the mySQL command prompt shows them as DATE, is this the problem? What type should I set them to?

Upvotes: 0

Views: 1576

Answers (2)

jaczes
jaczes

Reputation: 1394

correcting @Akhil post:

INSERT INTO tblData_FY14 
VALUES (STR_TO_DATE('24/07/2013','%d/%m/%Y'), 
    CONCAT('0000-00-00 ',STR_TO_DATE('14:31:07','%H:%i:%s')), 
    CONCAT('0000-00-00 ',STR_TO_DATE('14:31:20','%H:%i:%s')), 
    1, 1, 2, 3, 4, 13);
  • EDIT: it's pointless to have "DateofStop DATE, " & "TimeofStop DATETIME" collumns

Upvotes: 0

Akhil
Akhil

Reputation: 2602

Use %H insteadd of %h


%H uses 24 hour format wherein %h uses 12 Hour format

INSERT INTO tblData_FY14 
VALUES (STR_TO_DATE('24/07/2013','%d/%m/%Y'), 
        STR_TO_DATE('14:31:07','%H:%i:%s'), 
        STR_TO_DATE('14:31:20','%H:%i:%s'), 
        1, 1, 2, 3, 4, 13)

Upvotes: 1

Related Questions