mysql - 不正确的日期值 : '2016-06-05T00:00:00+05:30' for column '' in MySql

Question

我有 Windows 窗体应用程序和 mysql 数据库。我使用 xml 字符串来保存表单的详细信息部分，这里是我的 xml

> <DocumentElement>   <Table1>
>     <Qualification>fef</Qualification>
>     <University>fewfewf</University>
>     <Subject>fefwe</Subject>
>     <Grade>ffw</Grade>
>     <Duration>fef</Duration>
>     <StartDate>2016-06-05T00:00:00+05:30</StartDate>
>     <EndDate>2016-06-05T00:00:00+05:30</EndDate>   </Table1> </DocumentElement>

在我的存储过程中我有这段代码

 -- calculate the number of row elements. 
SET @v_row_count  := EXTRACTVALUE(QualificationsXML, CONCAT('count(','/DocumentElement/Table1)'));  
SET @v_row_iterator :=0;
SET @v_node := '/DocumentElement/Table1';

 -- loop through all the row elements    

WHILE @v_row_iterator < @v_row_count DO
    SET @v_row_iterator:=@v_row_iterator+1;
    SET @v_row := CONCAT(@v_node,'[',@v_row_iterator,']');

    INSERT INTO emp_qualifications VALUES (
        EmpNo,
        EXTRACTVALUE(QualificationsXML, CONCAT(@v_row,'/Qualification')),
        EXTRACTVALUE(QualificationsXML, CONCAT(@v_row,'/University')),
        EXTRACTVALUE(QualificationsXML, CONCAT(@v_row,'/Subject')),
        EXTRACTVALUE(QualificationsXML, CONCAT(@v_row,'/Grade')),
        EXTRACTVALUE(QualificationsXML, CONCAT(@v_row,'/Duration')),
        EXTRACTVALUE(QualificationsXML, CONCAT(@v_row,'/StartDate')),
        EXTRACTVALUE(QualificationsXML, CONCAT(@v_row,'/EndDate'))
    );

END WHILE;   

但是当我尝试保存它时会出现这个错误

Incorrect date value: '2016-06-05T00:00:00+05:30' for column 'StartDate' at row 1

在我的表结构中，我有两列要存储 StartDate 和 EndDate

> `StartDate , Date,nullable 
> `EndDate ,Date,nullable

谁能帮帮我？

Answer 1

我通过使用删除了字符串的时间部分

SUBSTRING_INDEX(EXTRACTVALUE(QualificationsXML, CONCAT(@v_row,'/StartDate')),'T',1),

SUBSTRING_INDEX(EXTRACTVALUE(QualificationsXML, CONCAT(@v_row,'/EndDate')),'T',1)

谢谢@peter4499