ISO-8601 DateTime to MySQL DateTime

Question

I am trying to insert data to MySQL using a stored procedure. The text values are getting inserted but the datetime has an issue. Here is the part of the code.

CALL `usp_UpdateStudent`("
                    <ArrayOfStudent>
                        <Student>
                            <ReadTime>2017-03-31T13:14:27Z</ReadTime>
                        </Student>
                        <Student>
                            <ReadTime>2017-03-31T13:14:27Z</ReadTime>
                        </Student>
                    </ArrayOfStudent>");

Stored Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_UpdateStudent`(tblStudent  longtext)
    BEGIN
        DECLARE k INT UNSIGNED DEFAULT 0;
        DECLARE row_count INT UNSIGNED;
        DECLARE xpath TEXT;

        DROP TEMPORARY TABLE IF EXISTS `StudentTemp`;
        CREATE TEMPORARY TABLE StudentTemp (ReadTime datetime);

        SET row_count := extractValue(tblTag,'count(/ArrayOfStudent/Student)');

        WHILE k < row_count DO        
        SET k := k + 1;
        SET xpath := concat('/Student[', k, ']');

        INSERT INTO StudentTemp VALUES (
            DATE_FORMAT(extractValue(tblTag, concat(xpath,'/ReadTime')),'%Y-%m-%d %H:%i:%s')
        );
        END WHILE;
        insert into student select * from StudentTemp;
    END

Table

CREATE TABLE `student` (
                        `ReadTime` datetime DEFAULT NULL
                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Error

Error Code: 1292. Incorrect datetime value: ''

You're using '%Y-%m-%d %H:%i:%s' as your date/time format but wouldn't you have to use '%Y-%m-%dT%H:%i:%sZ'? I'm not sure how MySQL handles literals in date/time strings but I wouldn;t expect that you simply ignore those two characters. — jmcilhinney, Apr 01 '17 at 05:36
@john The input string which contains the datetime is from c# — Gopi, Apr 01 '17 at 05:38
Google suggests that you can use `CAST("2011-10-02T23:25:42Z" AS DATETIME)` to convert it. — ProgrammingLlama, Apr 01 '17 at 05:42
@john Thanks. When I do SELECT CAST("2011-10-02T23:25:42Z" AS DATETIME); I get the output as '2011-10-02 23:25:42' but INSERT INTO student VALUES (CAST("2011-10-02T23:25:42Z" AS DATETIME)); results in Error Code: 1292. Truncated incorrect datetime value: '2011-10-02T23:25:42Z' — Gopi, Apr 01 '17 at 05:45
Make some debugging and check if extractValue returns correctly the ReadTime value — Abdullah Dibas, Apr 01 '17 at 05:47
Hmm, try this answer: http://stackoverflow.com/questions/28108605/mysql-issue-updating-datetime-field-from-iso-8601-format — ProgrammingLlama, Apr 01 '17 at 05:47

ISO-8601 DateTime to MySQL DateTime

0 Answers0