SQL Query with Date returns Datetime in Node mssql

  express, javascript, node-mssql, sql-server

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)
  }

}

Source: Ask Javascript Questions

LEAVE A COMMENT