Hi I need some fix here to get the proper result for grouping dates in my BAR chart. I need to group them according to dates, not in date and time.
I used this query in mysql and it works, however applying to SSRS Query Builder it returns an sql error.
Error[07002][MySQL][ODBC 3.51 Driver][mysql-5.0.45-community-nt]SQLBindParameter not used for all parameters
SELECT COUNT(*) AS total_exam, DATE(labcollect) AS collecteddate
FROM labinpat
WHERE (labcollect BETWEEN ? AND ?)
GROUP BY DATE(labcollect)
BUT this one works fine but doesn't get the result I wanted:
SELECT COUNT(*) AS total_exam, labcollect AS collecteddate
FROM labinpat
WHERE (labcollect BETWEEN ? AND ?)
GROUP BY labcollect
I tried to change the query to this:
SELECT COUNT(*) AS total_exam, CONVERT(labcollect, DATETIME) AS collecteddate
FROM labinpat
WHERE (labcollect BETWEEN ? AND ?)
GROUP BY CONVERT(labcollect, DATETIME)
but still result is the same in the screenshot.
i want the 12/25/2017 dates be merged as one group using my query
I'm not hugely familiar with MySQL and I only rarely use Query Builder in SSRS but the problem is possibly:
GROUP BY CONVERT(labcollect, DATETIME)
which is not T-SQL syntax. In T-SQL you'll need to use
CAST(labcollect AS DATE)
in your SELECT list and GROUP BY clause to remove the time component.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537