Error code 1292 Mysql DateTime

Question

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.

score 2 · Accepted Answer · answered Feb 27 '13 at 09:39

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.

@user2006436: I don't know, to be honest - it's worth trying. You'll need to split by `/` though, as the month width will be variable... — Jon Skeet, Feb 27 '13 at 10:22

score 1 · Answer 2 · answered Feb 27 '13 at 09:39

1

Somehow your LMONTH and LDAY values seem to be reversed as LMONTH is getting written as 31 and LDAY as 01 - which obviously incorrect. Check the data within your source table.

answered Feb 27 '13 at 09:39

Anonymous

575
1
4
11

Error code 1292 Mysql DateTime

2 Answers2

Linked