It just dawned on me that I mistakenly posted the wrong code for my question earlierDespite setting the BETWEEN method in my statement, I am still getting random months when trying to run it in my nodejs with mysql2. Why is that happening?
SELECT
u.department,
YEAR(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y')) AS year,
DATE_FORMAT(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y'), '%b') AS month,
COUNT(*) AS event_count
FROM
userlibrary tlc
LEFT JOIN
users u ON tlc.userfullname = u.fullname
WHERE
u.type = 'STUDENTS'
AND YEAR(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y')) BETWEEN ? AND ?
GROUP BY
u.department,
year,
month
ORDER BY
u.department,
year,
month;
`;
const [results, fields] = await connection.query(sql, [
type,
`${startYear}-${from}`,
`${endYear}-${end}`,
]);
*http://localhost:5000/api/result/table?from=01&startYear=2023&end=02&endYear=2023&type=students
This is my code.
To make the query call dynamic, I used the following configuration:
- from: 01
- end: 02
- startYear: 2022
- endYear: 2023
Upon running the code, I observed that the results I obtained include months that fall outside the specified dates, as illustrated in the server-side output below:
{
"results": [
{
"department": "College 1",
"year": 2023,
"month: "Apr",
"event_count: 25
},
{
"department": 2023,
"month: "Jan",
"event_count: 19
},
...
]
It is evident from the results that there are months other than Jan and Feb that have been included in the output.