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

Mysql – Split string to only get positions 1, 2 and 3, removing everything else

I am using nodejs + mysql, and I want to show a date in a json api.

The date is not a string, is a mysql date type.

E* I can’t select date(columnname) because I am already selecting everything from the table. This is my query:

var query_ = `
SELECT p.*, GROUP_CONCAT(ss.name) AS technologies 
FROM Projects_Project AS p
JOIN Projects_rel_Project_Skill AS prps ON prps.Project = p.ProjectID
JOIN Skills AS ss ON ss.skillID = prps.Skill
GROUP BY p.ProjectID
ORDER BY p.date DESC;`;

var mylist = results.map(row => ({
    'date': row.date
}));

This will output:
"2022-08-24T05:00:00.000Z"

So my attempt was to use .split after the T, but it gives an error saying that .split can only be used to strings and date is not a string.

var mylist = results.map(row => ({
    'date': row.date.split('T')[0]
}));

So searching more I attempt to convert it to string, but the output is different.

var mylist = results.map(row => ({
    'date': row.date.toString()
}));

"Wed Aug 24 2022 00:00:00 GMT-0500 (Central Daylight Time)"

So now the problem is that I only want "Aug 24 2022" and I don’t know how to do it. Using .split(' ')[1] will only give me "Aug"

I also attempt to do .split(' ')[1, 2, 3] and .split(' ')[1][2][3] but nothing.

Tags:

3

Answers


  1. 0

    you need to slice your splitted date

    wantedDate = .split(' ').slice(1, 3)

    then you can join your wantedDate to get the string date

    Login or Signup to reply.
  2. 0

    You could try using toLocaleDateString to format the date. For example:

    date: row.date.toLocaleString('en-US', {
  day: 'numeric',
  month: 'short',
  year: 'numeric'
});

    This would output Aug 24, 2022.

    See here for more info https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toLocaleDateString

    Login or Signup to reply.
  3. 0

    row.date is a Date object, you can convert it into string with toISOString() method:

    const now = new Date();
console.log(now.toISOString().split("T"))

    So in your case it would be:

    var mylist = results.map(row => ({
    'date': row.date.toISOString().split('T')[0]
}));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search