mysql: data truncated error from time value: function

Question

My function is has two variables ('date', number of months to subtract from 'date').

here is my function:

create function a_testbed.PrevMonth (
in_date         date,
in_mn_count     int)
RETURNS int
BEGIN
/* local variable declaration */
declare v_date         int(255);
declare v_date_format  varchar(10);
declare v_date_sub     varchar(10);

set v_date_format := DATE_FORMAT(v_date, '%Y-%m');
set v_date_sub := DATE_FORMAT(curdate(), '%Y-%m');

if (in_date is null) then
  set v_date := v_date_sub;
elseif (in_mn_count is null or in_mn_count < 0) then
  set in_mn_count := 0;
else
  set v_date := v_date_sub;
end if;
RETURN v_date_format;
END;
#

Keep getting the error:

ERROR 1265 (01000) Data Truncated for column 'v_date' at row 1

any thoughts?
thanks!

Answer 1

I don't believe "INT(255)" is a valid datatype.

I know the 255 is just a display width attribute. The INT datatype defines the range of values, the display width doesn't impact the range of values. But I've just never seen the length modifier on an INT larger than 11. (10 for an unsigned int) (?)

As a test, you could try specifying INT(11), or just INT.

More problematic, perhaps, is that this variable with datatype INT is being passed as the first argument to DATE_FORMAT function. But the first argument of DATE_FORMAT needs to be a DATE, DATETIME or TIMESTAMP (iirc). There may be some implicit conversion from INT to DATE, but I don't think there is.

---

I don't understand why you need a function to subtract a number of months from a DATE. MySQL has builtin in functions, and simple expressions that do that.

For example:

dateexpr + INTERVAL -6 MONTH

returns a DATE (or DATETIME) value with 6 months subtracted from dateexpr. If you need to "round down" to the first of the month, or convert to a string with just the 'yyyy-mm', you can do a DATE_FORMAT(dateexpr,'%Y-%m-01') or a SUBSTRING(dateexpr,1,7), respectively.

---

review

CREATE FUNCTION a_testbed.PrevMonth (
in_date         date,
in_mn_count     int)

That looks okay, two input parameters, a DATE and an INT. (Function parameters are always IN parameters.)

RETURNS int

A bit odd that a function named PrevMonth takes a DATE argument, but returns an INT, rather than a DATE, but this isn't an error.

BEGIN
/* local variable declaration */
DECLARE v_date         INT(255);

The display width of 255 is bizzarre. Never seen that on an INT before.

DECLARE v_date_format  VARCHAR(10);
DECLARE v_date_sub     VARCHAR(10);

Two strings, looks fine.

SET v_date_format := DATE_FORMAT(v_date, '%Y-%m');
                                 ^^^^^^

But v_date is an INT, not a DATE or DATETIME. And it's not initialized, so it's NULL. If this doesn't throw an exception, then we'd expect v_date_format to be NULL. (I suspect you wanted to specify in_date here in place of v_date.)

SET v_date_sub := DATE_FORMAT(curdate(), '%Y-%m');

This should get us 7 characters, e.g. '2013-07'. So no problem there.

IF (in_date IS NULL) THEN
    SET v_date := v_date_sub;

Here's a usage of the in_date parameter. Check if it's null. If it is, assign the same 7 characters that we assigned to v_date_sub. Got it.

ELSEIF (in_mn_count IS NULL OR in_mn_count < 0) THEN
    SET in_mn_count := 0;

Is it valid to assign a value to an IN parameter? (That may be valid, but it is odd. Normally we don't assign values to parameters unless they are OUT, or INOUT paremeters. We normally assign values to procedure variables (and sometimes MySQL user variables.)

Why is this dependent on whether in_date is null or not (the preceding IF). Seems like we would want to check this parameter in either case.

ELSE
  SET v_date := v_date_sub;

Isn't this the same action specified under the first IF? It seems like the logic here reads "if in_date is not null and ifnull(in_mn_count,-1)<0" we do one thing, else we do somthing else. We're either trying to assign a value to an IN parameter, or we're assigning current YYYY-MM to v_date.

END IF;

So far, the only use of the in_date parameter has been to check if it is NULL. And the only usage of the in_mn_count parameter has been to try to assign a zero to it, if it's null or less than zero.

RETURN v_date_format;

The only time we assigned anything to v_date_format was at the beginning of the function, when we assigned the return from a DATE_FORMAT function, passing in a NULL value (as an INT rather than a DATE.) So what was all the other code supposed to be doing?

END

Yes, please, end. There is so much wrong in this function, it makes my head hurt. So, please, yes, END. Just make it stop.

Answer 2

There are lots of things wrong with your function:

1. You do set v_date_format := DATE_FORMAT(v_date, '%Y-%m');, but this is before you assignea value to v_date.

2. Later you assign set v_date := v_date_sub;, but you never use v_date after that, so what is the point?

3. You assign set in_mn_count := 0;, but never use this variable, either.

4. Finally you do RETURN v_date_format;. v_date_format is a VARCHAR, but the function is declared to return INT. And the contents of this string is in the form YYYY-MM, so it doesn't look like an integer.

I'm not sure if any of these are causing the Data truncated error, but that hardly seems to be important when the function is so broken.

Answer 3

You assign varchar to int set v_date := v_date_sub; Change this:

declare v_date         varchar(10);