SQL Query with Date returns Datetime in Node mssql

Question

Im using v6.3.1 of node mssql. My query includes multiple columns of the type date. In node mssql the output of all Date columns are in the format: 2020-10-20T00:00:00.000Z When I make the same query in Azure Data Studio I get: 2020-10-20

My problem is when I need to update the database as I get an error using the YYYY-MM-DD format. is there a way to update the database without having to check each field if its a date and then add "0T00:00:00.000Z" to it?

Current code is this:

// Runs at server startup

const sql = require('mssql')

const poolPromise = sql.connect({
  server: process.env.SQL_SERVER,
  user: process.env.SQL_USER,
  password: process.env.SQL_PASSWORD,
  database: process.env.SQL_DATABASE
})

// Runs at query

async function updateSqlRecord(fields) {
// Adding fields below for demonstration
  let fields = {id: 1, name: 'test', date: '2020-10-12' }

  let database = process.env.SQL_DATABASE
  let table = 'Test'
  let querystring = `UPDATE [${database}].[dbo].[${table}] SET `

  Object.entries(fields).forEach(field => {
    const [key, value] = field;
    querystring += `${key} = '${value}', `
  });

  querystring = querystring.slice(0, -2)
  querystring += ` WHERE projektNr = ${fields.projektNr}`
  try {
    let pool = await poolPromise
    let result = await pool.request()
      // .input('projektNr', sql.Int, value)
      .query(querystring)
    console.log(result)
    return result.rowsAffected
  } catch (err) {
      console.log('SQL request Error',err)
  }

}

Answer 1

If possible you could try to use moment.js to parse the date before adding it to the database

var moment = require('moment');
...
var formatedDate = moment(myDate).format('YYYY/MM/DD HH:MM:SS').toISOString();