Shivpal singh Rathore Shivpal singh Rathore - 19 days ago 10
MySQL Question

Sql server Query in mysql

I have a query which work fine with sql server but error in mysql

this is the error


com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Incorrect parameter count in the call to native function 'DATE_FORMAT'


And this is my query:

String sql = "SELECT SUM(Pay) AS Pay, ClinicId, DATE_FORMAT
(DATENAME(MONTH, Date)) as Months "
+"FROM Prescription GROUP BY DATE_FORMAT (DATENAME(MONTH,
Date)), ClinicId HAVING ClinicId = '"+cid+"'";

Answer

Had you checked out the MySQL documentation on date_format() function, you would have noticed that it requires 2 parameters, not just one:

DATE_FORMAT(date,format)

Formats the date value according to the format string.

You probably need the date_format(Date, '%M') expression. The linked documentation contains all the formatting options, so you can play around with it to tailor the output to your needs.

Comments