问题描述
我正在尝试将日期为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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！