views:

211

answers:

4

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.

+2  A: 

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?

Raffael Luthiger
Yeah, thanks. I fixed that.
John Fiala
A: 

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.

DreadPirateShawn
Yeah, when I run the 'SELECT' query by itself on the data, I definately get nice dates.
John Fiala
+1  A: 

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?

Raffael Luthiger
I remember running it without the IGNORE once and not noticing any errors, but I'll try that again on Monday. expiredate on the source table is MMM DD like 'Aug 10', expiredate on the target table is a datetime field.
John Fiala
A: 

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.

John Fiala