1292 mysql_错误代码1292 Mysql DateTime

I am trying to format a date and a time that comes in one column called DATE as DD/MM/YYYY (Varchar) and in another column called TIME as HH:MM:SS into one variable to insert into another column (in Datetime data type). The code below is my procedure.

DROP PROCEDURE IF EXISTS TESTProc;

DELIMITER //

CREATE PROCEDURE TESTproc()

BEGIN

DECLARE LYEAR VARCHAR(45);

DECLARE LMONTH VARCHAR(45);

DECLARE LDAY VARCHAR(45);

DECLARE LTIME VARCHAR(45);

DECLARE LDATETIME DATETIME;

SELECT TIME FROM db.test_table INTO LTIME;

SELECT SUBSTRING(DATE,6,4) FROM db.test_table INTO LYEAR;

SELECT SUBSTRING(DATE,3,2) FROM db.test_table INTO LMONTH;

SELECT SUBSTRING(DATE,1,1) FROM db.test_table INTO LDAY;

SELECT CONCAT(LYEAR,'-', LMONTH,'-','0',LDAY,' ',LTIME) INTO LDATETIME;

INSERT INTO db.test_table(VC19) VALUES (LDATETIME);

END //

Call TESTProc;

When I run the procedure, I get an error code back:

Call TESTProc; Error Code: 1292. Incorrect datetime value: '2013-31-01 16:00:40' for column 'LDATETIME' at row 2

I only have one row in db.test_table. I do not have a column in the table called 'LDATETIME', this is just my local variable. I can see from the error that my format is correct for the DateTime 'YYYY-MM-DD HH:MM:SS'.

why I am getting this error?

Update: Here is how my code looks now:

DROP PROCEDURE IF EXISTS DateProc;

DELIMITER //

CREATE PROCEDURE Dateproc()

BEGIN

DECLARE LTIME VARCHAR(45);

DECLARE LDATE VARCHAR(45);

DECLARE LDATETIME DATETIME;

SELECT TIME FROM db.date_table INTO LTIME;

SELECT DATE FROM db.date_table INTO LDATE;

IF LENGTH(LDATE) = 9 AND SUBSTRING(LDATE,2,1) = '/'

THEN SET LDATETIME = CONCAT(SUBSTRING(LDATE,6,4),'-0',SUBSTRING(LDATE,1,1),'-',SUBSTRING(LDATE,3,2), ' ', LTIME);

ELSE IF LENGTH(LDATE) = 9 AND SUBSTRING(LDATE,3,1) = '/'

THEN SET LDATETIME = CONCAT(SUBSTRING(LDATE,6,4),'-',SUBSTRING(LDATE,1,2),'-0',SUBSTRING(LDATE,4,1), ' ', LTIME);

ELSE IF LENGTH(LDATE) = 10

THEN SET LDATETIME = CONCAT(SUBSTRING(LDATE,7,4),'-',SUBSTRING(LDATE,1,2),'-',SUBSTRING(LDATE,4,2), ' ', LTIME);

ELSE IF LENGTH(LDATE) = 8

THEN SET LDATETIME = CONCAT(SUBSTRING(LDATE,5,4),'-0',SUBSTRING(LDATE,1,1),'-0',SUBSTRING(LDATE,3,1), ' ', LTIME);

END IF;

END IF;

END IF;

END IF;

INSERT INTO db.date_table(table_name) VALUES (LDATETIME);

END //

CALL DateProc;

This seems to work and accounts for any variable date that may end up in my original date column.

解决方案

Look at the value:

'2013-31-01 16:00:40'

That's trying to use a month of 31.

It's not clear whether that just means your test data is wrong, or whether you need to change these lines:

SELECT SUBSTRING(DATE,3,2) FROM db.test_table INTO LMONTH;

SELECT SUBSTRING(DATE,1,1) FROM db.test_table INTO LDAY;

to:

SELECT SUBSTRING(DATE,1,2) FROM db.test_table INTO LMONTH;

SELECT SUBSTRING(DATE,4,2) FROM db.test_table INTO LDAY;

Note the change from 1 to 2 for the substring starting at 1 anyway, and the change of the second starting position from 3 to 4. You want two-digit month and day values, right? If your data format is actually D/M/YYYY (i.e. only using two digits when they're required) then you won't be able to use fixed substring positions.