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.