mysql日期加时间字符串连接_mysql-如何连接字符串并转换为日期字符串？

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)

I'm trying to get the monthly score of any ninja based on the ninjaId.

Why is not working? Any idea where I am getting wrong?

解决方案

CONCAT() is the key.

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)