Stored function in MySQL doesn't like user supplied dates?

Question

I have a stored function in MySQL:

CREATE FUNCTION `login_count`(o INT, start_date DATE, end_date DATE) RETURNS INT
READS SQL DATA
BEGIN
DECLARE total_count INT;
SELECT COUNT(*) INTO total_count FROM logins as l WHERE `order_id` = o && modified BETWEEN start_date AND end_date; 
RETURN total_count; 
END

Pretty basic, takes an id, start date, and an end date and returns the count of logins for that date range. Whenever I run it I get a 0 back. Unless I removed the date section of the where clause. Then it returns the an actual number. OR if I just manually put the dates in the stored function it works... So it's not an issue of dates, but only when I'm supplying the dates through the parameter list it doesn't like it.

Any thoughts as to what would cause this to happen? The fact that I can just manually put the dates in the stored function and it works really bugs me. It's not like a lot is happening here that could mess up so I'm kind of lost as what to try next.

Also, is there a way to debug stored functions/procedures. I'm just getting a 0 back, but is there a way for me to debug this to try and figure out what might be happening?

Answer 1

My first guess is that you're supplying dates in a format that MySQL doesn't recognize. MySQL is pretty picky about the format of date literals. It wants YYYY-MM-DD, YY-MM-DD, YYYYMMDD, or YYMMDD (you can also use any other punctuation character in place of the dashes). See The DATETIME, DATE, and TIMESTAMP Types for more details.

You can also convert more date formats to the MySQL-accepted format with the STR_TO_DATE() function.

Regarding debugging stored procedures and stored functions, you can always set a user-defined variable with the @ prefix, and this variable becomes globally available after the stored function returns. Like a side-effect. So you can use this for setting diagnostic information while running your stored function, and then check it afterward.