MySQL, Date, Prepared Statement oddity

Databases
1

I need to import a tab delimited text file of data into MySQL using a Prepared Statement in Xojo. One of the columns in MySQL is a date column. The input data has the date formatted as YYYY-MM-DD. Sometimes there is a date like this: 2018-05-10. When there isn’t the input data looks like this: 0000-00-00. When it is like this what I always end up with in MySQL is 0002-11-30.

If I insert a row of data manually and the empty date is 0000-00-00 then I get exactly that. If I use the prepared statement I get the 0002-11-30 for all dates entered as 0000-00-00.

I’ve tried every permutation of allowing NULL or not, having a default value or not, and varying the input. Whatever the combination I get one of 2 results. I’ll get this crazy date of 0002-11-30 or I will get an Unsupported Format Exception and the application closes.

I’m starting to think that this is a bug in prepared statement and the solution would be to set a trigger to convert the date from 0002-11-30 to 0000-00-00.

I’d be happy to hear comments on this. Thanks.

2

If I recall correctly, its a bug in mySQL itself.
years ago I was the webmaster for an international special interest group, and I wrote all the web software myself using PHP and mySQL. There seemed to be no way to set a Date field to NULL except during the CREATE TABLE phase… And one of the things I was tracking was membership expirations, which required the expire date to be set to NULL if the member renewed their account.
The solution (and it may not work for you) was to use a valid but bogus date like “4999-12-31” that was so far in the future as to never be possible in reality.

3

Sounds like this bug in mysql (maybe the one that Dave mentioned), one of the results from a search for this:

https://bugs.mysql.com/bug.php?id=3331

4

Thanks. I can probably live with it as 0002-11-30 is a “bogus enough” date that it won’t get mistaken.

Thanks for the reference. I did not come up with that in my searching. :expressionless:

It’s curious that if I execute the SQL in Workbench it works correctly.

5

You could try enabling the General Query Log to see what Workbench is really doing behind the scenes.

6

I’m starting to think that it’s the Prepared Statement that’s the problem. That’s the only way it’s not working.

7

It looks like if I add 00:00:00 after the 0000-00-00 it works. That’s not how the insert data comes in but I can deal with that.