mysql - How to concatenate strings and convert to date the strings?

mysql, stored-procedures

Solution

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)

Problem

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?

Original source