Why is this mysql table producing a NULL in the datetime field?

Question

I've got this table. The table has a bunch of char fields, but the field in question (expiredate) contains a char on the test_tmp table, and a datetime on the test table. (Table names have been changed to protect the clients.)

In the test_tmp table, the field contains a Month-Date pair, like 'Aug 10' or 'Feb 20'. The code I'm using to insert the data is:

INSERT IGNORE INTO test (RECNUM, ORDERNUM, CREATEDATE, EXPIREDATE, FNAME, LNAME)
  SELECT RECNUM, ORDERNUM, CREATEDATE,
    CAST(CASE WHEN str_to_date(concat(expiredate, ' ', year(now())), '%b %d %Y') <= CURDATE()
    THEN str_to_date(concat(expiredate, ' ', 1+year(now())), '%b %d %Y')
    ELSE str_to_date(concat(expiredate, ' ', year(now())), '%b %d %Y') END AS DATETIME) AS EXPIREDATE, FNAME, LNAME FROM test_tmp

What's really puzzing is that if I run the 'SELECT' section of the query everything looks fine. But if I run the whole thing (truncating the test table first), every row contains NULL in expiredate.

Answer 1

Sadly I can not set comments yet. That's why I have to write it here.

At the end of the SQL statement you have a ')' to much. Is there maybe more cut out while you copy/pasted this code?

Answer 2

To test your query, I ran the following:

SELECT CAST(CASE WHEN str_to_date(concat('Jan 5', ' ', year(now())), '%b %d %Y') <= CURDATE()
    THEN str_to_date(concat('Jan 5', ' ', 1+year(now())), '%b %d %Y')
    ELSE str_to_date(concat('Jan 5', ' ', year(now())), '%b %d %Y') END AS DATETIME) AS EXPIREDATE;

This worked just fine. Just to make sure, EXPIREDATE should be in the form "Jan 5" etc, or else the NULL results from str_to_date failure.

Answer 3

Do you get any error messages?

Could you leave the IGNORE away and then see if you have any messages?

According to the MySQL website: Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort.

Such an adjustment could happen in your case. What kind of field is this expiredate exactly?

Answer 4

Well, I just found the answer to my own question, finally.

Notice how I put the 'drupal' tag on the question? The query was going through Drupal... which requires you to escape the % character if you don't want it used to replace data. So, str_to_date(concat(expiredate, ' ', year(now())), '%b %d %Y') actually needed to be str_to_date(concat(expiredate, ' ', year(now())), '%%b %%d %%Y').

Thanks for the suggestions, folks.