StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POError code 1292 Mysql DateTime
text
Body
copied!<p>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.</p> <pre><code>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; </code></pre> <p>When I run the procedure, I get an error code back:</p> <pre><code>Call TESTProc; Error Code: 1292. Incorrect datetime value: '2013-31-01 16:00:40' for column 'LDATETIME' at row 2 </code></pre> <p>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'. </p> <p>why I am getting this error?</p> <p>Update: Here is how my code looks now:</p> <pre><code>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; </code></pre> <p>This seems to work and accounts for any variable date that may end up in my original date column. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload