"2013-03-00" in `date` column returns "Invalid Date", but value is allowed in MySQL

[0b10011]

We use "2013-03-00" in a date column to denote books with a publish date in "March 2013" (due to not knowing the specific day, or the day being different depending location). With node-mysql, however, this returns "Invalid Date" (probably due to blindly feeding it into new Date()).

From the manual (source):

MySQL permits you to store dates where the day or month and day are zero in a DATE or DATETIME column. This is extremely useful for applications that need to store birthdates for which you may not know the exact date. In this case, you simply store the date as '2009-00-00' or '2009-01-00'. If you store dates such as these, you should not expect to get correct results for functions such as DATE_SUB() or DATE_ADD() that require complete dates. To disallow zero month or day parts in dates, enable the NO_ZERO_IN_DATE SQL mode.

[dougwilson]

You may just have to use a custom typecast to just keep those as strings, as JavaScript Date objects cannot handle those.

var mysql = require('mysql');
var connection = mysql.createConnection({
  // Your connection options here...
  typeCast: function (field, orig) {
    var value;
    if (field.type === 'DATE') {
      // DATE columns with 00 fields become strings, otherwise create Date object
      value = field.string();
      return /\-00/.test(value) ? value : orig();
    }
    return orig();
  }
});

You can also specify a type conversion per-query as well. For example, this query will just require all DATE fields as strings so it is consistent:

connection.query({
  sql: 'SELECT * FROM table;',
  typeCast: function (field, orig) {
    return field.type === 'DATE' ? field.string() : orig();
  }
})

[0b10011]

@dougwilson This was my planned workaround, but I figured it would be better to have this in the core, as it's MySQL's default behavior.

On that same note, though, when I first attempted the per-query typeCast, I couldn't figure out the format for using ? placeholders when including the typeCast as well (there was nothing in the docs about it). The following threw an error:

connection.query(
 {
  sql: "SELECT * FROM `table` WHERE `a` = ? LIMIT 1",
  typeCast: function (field, orig) {
    return field.type === 'DATE' ? field.string() : orig();
  }
 },
 [id],
 function(err, result) {

 }
);

I wouldn't be surprised if I was doing it wrong, but I'm not sure how to do it right.

[dougwilson]

Supply them with the key values in the object:

connection.query(
 {
  sql: "SELECT * FROM `table` WHERE `a` = ? LIMIT 1",
  values: [id],
  typeCast: function (field, orig) {
    return field.type === 'DATE' ? field.string() : orig();
  }
 },
 function(err, result) {

 }
);

[dougwilson]

Also, @felixge or @dresende would probably need to think about if supporting the dates "out-of-the-box" is doable.

[0b10011]

Great, I'll try that, thanks.

Also, this is closer to the approach I'm going to take until a fix from the core comes through... should catch everything:

connection.query({
  sql: "...",
  typeCast: function (field, next) {
   if (field.type === 'DATE') {
    if (field.string().substring(8, 10) === "00") { // Covers both 2013-00-00 and 2013-03-00, and special "0000-00-00"
     return field.string();
    }
   }
   return next();
  }
})

[dresende]

It would be good to support it, not sure how to handle it when inserting but probably just using a string as you really can't use a date object. When reading from database this is probably not difficult, since dates or either null or always "yyyy-mm-dd" (DATE), even if it's 0000-00-00 or yyyy-00-00. So if a date fails to parse with date we can just return it as string. What do you think @felixge ?

[dresende]

Let's continue in the pull request.