Question 113018 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql returns the date in this format "yyyy-mm-ddT00:00:00.000Z" I want it in "yyyy-dd-mm" format

so I am using MySQL as the database for my node app I can insert the date in YYYY-MM-DD format but when I get data from it returns it as yyyy-mm-ddT00:00:00.000Z so I want only the first part

db.query(`SELECT agereement_date FROM mt_A1 WHERE ledger_num = 15`,(err,data)=>{
  console.log(data)

})

the output is like this

[
  RowDataPacket {
    agereement_date: 2021-03-07T18:30:00.000Z,
  }
]

I Want only the YYYY-MM-DD the first part I am using some JavaScript to rectify it but it feels unnecessary is there a way to get the date in that format directly from MySQL

Tags:

2

Answers


  1. 0

    Time in MySQL db is in without definition of timezone.

    NodeJS converts time to Date object when You get it from server.

    So there are 3 solutions depending on Your use case:

    1. if You don’t care timezone of browser: You can add DATE_FORMAT to sql query and take date part only.

    2. it’s still not timezone aware, but You can take date from db on backend and cut date part of it using:

    db.query('SELECT agereement_date FROM mt_A1 WHERE ledger_num = 15', (err,data) => {

  data = data.map(row => {
    row.agereement_date = row.agereement_date.toISOString().split('T')[0];
    
    return row;
  });
  
  console.log(data);
})
    1. If You want to show correct time for timezone of browser then You must work with that date object on frontend level:
    function strftime(sFormat, date) {
  if (!(date instanceof Date)) date = new Date();
  var nDay = date.getDay(),
    nDate = date.getDate(),
    nMonth = date.getMonth(),
    nYear = date.getFullYear(),
    nHour = date.getHours(),
    aDays = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'],
    aMonths = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'],
    aDayCount = [0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334],
    isLeapYear = function() {
      if ((nYear&3)!==0) return false;
      return nYear%100!==0 || nYear%400===0;
    },
    getThursday = function() {
      var target = new Date(date);
      target.setDate(nDate - ((nDay+6)%7) + 3);
      return target;
    },
    zeroPad = function(nNum, nPad) {
      return ('' + (Math.pow(10, nPad) + nNum)).slice(1);
    };
  return sFormat.replace(/%[a-z]/gi, function(sMatch) {
    return {
      '%a': aDays[nDay].slice(0,3),
      '%A': aDays[nDay],
      '%b': aMonths[nMonth].slice(0,3),
      '%B': aMonths[nMonth],
      '%c': date.toUTCString(),
      '%C': Math.floor(nYear/100),
      '%d': zeroPad(nDate, 2),
      '%e': nDate,
      '%F': date.toISOString().slice(0,10),
      '%G': getThursday().getFullYear(),
      '%g': ('' + getThursday().getFullYear()).slice(2),
      '%H': zeroPad(nHour, 2),
      '%I': zeroPad((nHour+11)%12 + 1, 2),
      '%j': zeroPad(aDayCount[nMonth] + nDate + ((nMonth>1 && isLeapYear()) ? 1 : 0), 3),
      '%k': '' + nHour,
      '%l': (nHour+11)%12 + 1,
      '%m': zeroPad(nMonth + 1, 2),
      '%M': zeroPad(date.getMinutes(), 2),
      '%p': (nHour<12) ? 'AM' : 'PM',
      '%P': (nHour<12) ? 'am' : 'pm',
      '%s': Math.round(date.getTime()/1000),
      '%S': zeroPad(date.getSeconds(), 2),
      '%u': nDay || 7,
      '%V': (function() {
              var target = getThursday(),
                n1stThu = target.valueOf();
              target.setMonth(0, 1);
              var nJan1 = target.getDay();
              if (nJan1!==4) target.setMonth(0, 1 + ((4-nJan1)+7)%7);
              return zeroPad(1 + Math.ceil((n1stThu-target)/604800000), 2);
            })(),
      '%w': '' + nDay,
      '%x': date.toLocaleDateString(),
      '%X': date.toLocaleTimeString(),
      '%y': ('' + nYear).slice(2),
      '%Y': nYear,
      '%z': date.toTimeString().replace(/.+GMT([+-]d+).+/, '$1'),
      '%Z': date.toTimeString().replace(/.+((.+?))$/, '$1')
    }[sMatch] || sMatch;
  });
}



const time = new Date('2021-03-08T00:30:00.000+02:00');
const localTime = strftime('%Y-%m-%d %H:%M:%S', time);
const localDate = strftime('%Y-%m-%d', time);

console.log(localTime)

console.log(localDate)

    as You can see I’ve defined in ISO format with +02:00 so it may return differently depending on time of local computer.

    Login or Signup to reply.
  2. 0

    Modify your query :

    SELECT DATE(agereement_date) 
FROM mt_A1 
WHERE ledger_num = 15
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search