错误代码1292 Mysql DateTime [英] Error code 1292 Mysql DateTime

问题描述

我正在尝试将日期为DATE的日期和时间格式化为DD/MM/YYYY(Varchar)，并将日期为HH:MM:SS的另一列格式化为一个变量，以插入到另一列中(在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

db.test_table中只有一行.我在表中没有名为"LDATETIME"的列，这只是我的局部变量.从错误中可以看出，我的格式对于DateTime'YYYY-MM-DD HH:MM:SS'是正确的.

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.

推荐答案

查看值:

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

正在尝试使用31的 month .

目前尚不清楚这是否仅表示您的测试数据有误，或者是否需要更改这些行:

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;

收件人:

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

请注意，无论如何从1开始将子字符串从1更改为2，将第二个起始位置从3更改为4.您想要两位数的month and day值，对吗?如果您的数据格式实际上是 D/M/YYYY(即仅在需要时使用两位数字)，则您将无法使用固定的子字符串位置.

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.

这篇关于错误代码1292 Mysql DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！