mysql - 如何连接字符串并将字符串转换为日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10680961/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql - How to concatenate strings and convert to date the strings?
提问by NinjaBoy
Please take a look at my stored procedure code.
请看一下我的存储过程代码。
CREATE DEFINER=`ninjaboy`@`%` PROCEDURE `getMonthlyTotalScore`(IN ninjaId int, IN month int, IN year int)
BEGIN
DECLARE startDate DATE;
DECLARE endDate DATE;
DECLARE maxDay INTEGER;
SELECT DAY(LAST_DAY(year + '-' + month + '-01')) INTO maxDay;
SET startDate = year + '-' + month + '-01';
SET endDate = year + '-' + month + '-' + maxDay;
SELECT SUM(SCORE) FROM NINJA_ACTIVITY WHERE NINJA_ID = ninjaId AND DATE BETWEEN startDate AND endDate ORDER BY DATE;
END
Test Data:
测试数据：
NINJA_ACTIVITY_ID | NINJA_ID | SCORE | DATE 1 1 24 2012-05-01 2 1 36 2012-05-06 3 1 29 2012-05-11
Function call : call getTotalMonthlyScore (1, 5, 2012)
函数调用： call getTotalMonthlyScore (1, 5, 2012)
I'm trying to get the monthly score of any ninja based on the ninjaId.
我试图根据ninjaId.
Why is not working? Any idea where I am getting wrong?
为什么不工作？知道我哪里出错了吗？
回答by beck03076
CONCAT() is the key.
CONCAT() 是关键。
BEFORE:
前：
mysql> CREATE PROCEDURE `getMonthlyTotalScore`(IN ninjaId int, IN month int, IN year int)
-> BEGIN
-> DECLARE startDate DATE;
-> DECLARE endDate DATE;
-> DECLARE maxDay INTEGER;
->
-> SELECT year + '-' + month + '-01'; #NOTE THIS
->
->
-> END;
-> |
Query OK, 0 rows affected (0.00 sec)
mysql> call getMonthlyTotalScore(1,5,2012);
-> |
+----------------------------+
| year + '-' + month + '-01' |
+----------------------------+
| 2016 |
+----------------------------+
1 row in set (0.00 sec)
AFTER:
后：
mysql> CREATE PROCEDURE `getMonthlyTotalScore`(IN ninjaId int, IN month int, IN year int)
-> BEGIN
-> DECLARE startDate DATE;
-> DECLARE endDate DATE;
-> DECLARE maxDay INTEGER;
->
-> SELECT CONCAT(year,'-',month,'-01'); # NOTE THIS
->
->
-> END; |
Query OK, 0 rows affected (0.00 sec)
mysql> call getMonthlyTotalScore(1,5,2012);
-> |
+------------------------------+
| CONCAT(year,'-',month,'-01') |
+------------------------------+
| 2012-5-01 |
+------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
回答by John Woo
Instead of:
代替：
SELECT DAY(LAST_DAY(year + '-' + month + '-01')) INTO maxDay;
replace it with
将其替换为
SET maxDay := DAY(LAST_DAY(CAST(CONCAT(year, '-',month,'-01) as DateTime)));
回答by NinjaBoy
After getting the idea from beck's answer I have this working solution.
从贝克的回答中得到想法后，我有了这个可行的解决方案。
CREATE DEFINER=`ninjaboy`@`localhost` PROCEDURE `getMonthlyTotalScore`(IN ninjaId int, IN month int, IN year int)
BEGIN
DECLARE startDate DATE;
DECLARE endDate DATE;
DECLARE maxDay INTEGER;
SELECT DAY(LAST_DAY(CONCAT(year,'-',month,'-01'))) INTO maxDay;
SET startDate = CONCAT(year,'-',month,'-01');
SET endDate = CONCAT(year,'-',month,'-',maxDay);
SELECT SUM(SCORE) FROM NINJA_ACTIVITY WHERE NINJA_ID = ninjaId AND DATE BETWEEN startDate AND endDate ORDER BY DATE;
END
