Why do I still get random months even though I set the BETWEEN method in my statement when I tried to run in my nodejs? I am using mysql2
This is mysql call. This is working when I used the workbench. I get the data I want.
SELECT
DATE_FORMAT(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y'), '%Y-%m') as formatted_month_year,
tlc.userfullname,
//rest of the code
FROM
userlibrary tlc
LEFT JOIN
users u ON tlc.userfullname = u.fullname
WHERE
u.type = 'students'
AND DATE_FORMAT(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y'), '%Y-%m') BETWEEN '2022-11' AND '2023-04';
Now in my nodejs, What I do is get the url and use it as a query
*http://localhost:5000/api/result/table?from=01&startYear=2023&end=02&endYear=2023&type=students
This is my code.
const sql = `
SELECT
DATE_FORMAT(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y'), '%Y-%m') as formatted_month_year,
tlc.userfullname,
u.department,
u.type,
tlc.affecteduser,
tlc.eventcontext,
tlc.component,
tlc.eventname,
tlc.description,
tlc.origin,
tlc.ipaddress
FROM
userlibrary tlc
LEFT JOIN
users u ON tlc.userfullname = u.fullname
WHERE
u.type = ?
AND DATE_FORMAT(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y'), '%Y-%m') BETWEEN ? AND ?;
`;
const [results, fields] = await connection.query(sql, [
type,
`${startYear}-${from}`,
`${endYear}-${end}`,
]);
So what I do is use this query, and then make the query call dynamic.
- from :
01
- end :
02
- startYear:
2022
- endYear:
2023
And this is the result I get.
Result
"results": [{
"department": "COLLEGE 1",
"year": 2023,
"month": "Apr",
"event_count": 42
},
{
"department": "COLLEGE 1",
"year": 2023,
"month": "Aug",
"event_count": 42
},
{
"department": "COLLEGE 1",
"year": 2023,
"month": "Dec",
"event_count": 10
},
{
"department": "COLLEGE 1",
"year": 2023,
"month": "Feb",
"event_count": 45
},
{
"department": "COLLEGE 1",
"year": 2023,
"month": "Jan",
"event_count": 28
},
{
"department": "COLLEGE 1",
"year": 2023,
"month": "Jul",
"event_count": 7
},
]