MYSQL returns full datetime string on SELECT query when column type is DATE

Question

I have a hard time finding answer to my question after many researches on google, but my terms do not seem accurate enough to yield the correct answers to my question.

I have a database storing information on customers: first name, last name, birthdate etc.

The problem is specific to my birthdate column. The type is set to DATE and for every results, everything shows up as a DATE in phpmyadmin, but when I use SELECT in my script to retrieve rows, the birthdate column is returned like if it was DATETIME type except when the value is 0000-00-00.

I am using node-mysql to retrieve results from mysql table in my app. I built a module on top of node-mysql to shorten required code for pooling, which is where DatabasePool function comes from. Here is a sample node.js code from which the behaviour is shown:

property: {
    get: function(id, callback) {
        mysql.DatabasePool(function() {
            this.query('SELECT birthdate FROM ICM_Contact WHERE ICM_contact_id = ? LIMIT 1', [id], function(err, rows, fields) {
                console.log(rows[0]);
                callback(null, ICM.contact.revive(rows[0]));
            })
        });
    }
}

For given date: 2015-08-11 it will return { birthdate: Tue Aug 11 2015 00:00:00 GMT-0400 (EDT) }, but for date 0000-00-00 it will return { birthdate: '0000-00-00' } as expected.

I don't have any clue why phpmyadmin displays the date correctly no matter what, but when I do the query from my script, it won't return the date correctly.

I have tried the following: datetime mySQL SELECT only date

property: {
    get: function(id, callback) {
        mysql.DatabasePool(function() {
            this.query('SELECT date(birthdate) FROM ICM_Contact WHERE ICM_contact_id = ? LIMIT 1', [id], function(err, rows, fields) {
                console.log(rows[0]);
                callback(null, ICM.contact.revive(rows[0]));
            })
        });
    }
}

But still the date is returned as datetime.

Answer 1

The reason why the full datetime is returned instead of just the date is that the node mysql module casts all mysql date / datetime data types into JavaScript Date objects. Alternative to the accepted solution, this feature can be turned off by setting dateStrings to true in the connections:

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydb',
    port: '3306',
    ...
    dateStrings: true
});

Answer 2

Did you try this?

property: {
    get: function(id, callback) {
        mysql.DatabasePool(function() {
            this.query('SELECT DATE_FORMAT(birthdate,\'%m-%d-%Y\') FROM ICM_Contact WHERE ICM_contact_id = ? LIMIT 1', [id], function(err, rows, fields) {
                console.log(rows[0]);
                callback(null, ICM.contact.revive(rows[0]));
            })
       });
    }
}