Search code examples
node.jsexpressexpress-handlebars

How to format the date with month/day/year from Sun May 04 1980 00:00:00 GMT+0500 (Pakistan Standard Time) in nodejs javascript mysql

This is the code of the main page like in the screenshot. Using mysql to get that data, it is a CRUD application with nodejs, express, mysql and bootstrap. Don't know how to format the date to (month/day/year) instead of (Sun May 04 1980 00:00:00 GMT+0500 (Pakistan Standard Time)). Please help. New to javascript programming, so i am struggling :( enter image description here

<div class="row">
  <div class="col-6"><h1>Users</h1></div>
  <div class="col-6 d-flex justify-content-end">
    <a href="/adduser" type="button" class="btn btn-primary align-self-center">+ add new user</a>
  </div>
</div>
<table class="table table-bordered">
  <thead class="thead-dark">
    <tr>
      <th scope="col">#</th>
      <th scope="col">First Name</th>
      <th scope="col">Last Name</th>
      <th scope="col">DoB</th>
      <th scope="col">Phone</th>
      <th scope="col" class="text-end">Action</th>
    </tr>
  </thead>
  <tbody>

    {{#each rows}}
    <tr>
      <th scope="row">{{this.id}}</th>
      <td>{{this.first_name}}</td>
      <td>{{this.last_name}}</td>
      <td>{{this.dob}}</td>
      <td>{{this.phone}}</td>
      <td class="text-end">
          <a href="/viewuser/{{this.id}}" type="button" class="btn btn-light btn-small"><i class="bi bi-eye"></i> View</a>
          <a href="/edituser/{{this.id}}" type="button" class="btn btn-light btn-small"><i class="bi bi-pencil-square"></i> Edit</a>
          <a href="/{{this.id}}" type="button" class="btn btn-light btn-small"><i class="bi bi-person-x"></i> Delete</a>
      </td>
    </tr>
    {{/each}}

  </tbody>
</table>

// View users
exports.view = (req, res) => {
    pool.getConnection((err, connection) =>{
    if(err) throw err; // not connected
    console.log('DB connected as ID' + connection.threadId);
    // User the connection
    connection.query('SELECT * FROM users ', (err, rows)=> {
    // When done with the connection, release it
    connection.release();
        if(!err){
            let removedUser = req.query.removed;
            res.render('home', { rows, removedUser });
        }
        else {
            console.log(err);
        }
        console.log('The data from user table: \n', rows);
    });
});
}
Solution

  • You can update the Date format in your SQL query while fetching values. Simply mention the required date format and pass the value to your view engine.

    connection.query('SELECT all_required_columns, DATE_FORMAT(dob, "%m/%d/%y") as dob FROM users ', (err, rows)=> {

    This should fetch all values from the DB with DOB format as mm/dd/yy.
    PS: The North Remembers