nodejs mysql insert null datetime field

Question

using mysql-ssh

How to insert DateTime field's value to null in MySQL through nodejs?

Tried following:

connection.query('INSERT INTO idea_steps (field1, field2) VALUES(?, ?)', ['value1', null])
connection.query('INSERT INTO idea_steps (field1, field2) VALUES(?, ?)', ['value1', ''])
connection.query('INSERT INTO idea_steps (field1, field2) VALUES(?, ?)', ['value1', 'null'])
connection.query('INSERT INTO idea_steps (field1, field2) VALUES(?, ?)', ['value1', undefined])

Table Structure:

field1 String
field2 DateTime Default null

Error:

for ''

Incorrect datetime value: \'\' for column \'field2\' at row 730

for undefined

Unknown column \'NaN\' in \'field list\'

for 'null'

Incorrect datetime value: \'null\' for column \'field2\' at row 730'

for null

Unknown column \'NaN\' in \'field list\'

Can anyone help me to insert DateTime field to null? I can't skip the field2 field. Let me know if it is even possible, if not I'll have to figure out something else.

Answer 1

The syntax of connection.query seems faulty over here.

It should be:

const sqlQuery = "INSERT INTO idea_steps (field1, field2) VALUES ?";
const values =  [ ['field1', 'NULL'] ];

con.query(sqlQuery, [values], function (err, result) {
    if (err) throw err;
    console.log("Number of records inserted: " + result.affectedRows);
  });