Problem In Query report

ERPNext
1

I have column named pay_date storing date (by default storing as YYYY-MM-DD) . I want to print the date in DD-MM-YYYY format.

When i am running this query:

SELECT DATE_FORMAT(pay_date, %d/%m/%Y) AS formatted_date FROM table name;

IN QUERY REPORT ITS GIVING ERROR

image
image1366×768 156 KB

But same is working in phpmyadmin or in mysql.

Any suggestions ??

2

hi @nikzz What is the query result dict returning in python code ? did you try the query with fraape console first ?

3

you mean via bench mysql…yes itsgiving values there

4

No. try

$bench console

in your terminal from your bench root… and you will get a python console. you can fire your frappe api functions here and test the result. try frapp.sql() or frappe.get_all here and play with python before you can put the code in your py.

Debug what you are getting in query result dict here and do the changes in your code.

Hope it helps.

Regards,

parth

5

Put your mysql query here.

6

in Query Report, you dont need to format your date in query. Just Set it’s Type to Date and frappe framework will take care of that.

pay_date as “Pay date:Date:100”,