Date Calculation in SQL Query Report

ERPNext Reports
1

Hi,

I have a very simple problem, and I seem to be going round in circles for it.

I have a report, where I need to add a column, Delay in Days, which will be the difference between Current Date and Delivery Date from Sales Order.

When I am doing a CURDATE() - tabSales Order Item.delivery_date, I am getting a huge number. I think its because Curdate shows a yyyy-mm-dd format, while the delivery date is in dd-mm-yyyy format. How can I change the format of Curdate inside Query Report?

Thanks
Uma

2

Hi,

Please close this issue as I was able to find the solution with DATEDIFF function, by using DATEDIFF(CURDATE(), tabSales Order Item.delivery_date)

Thanks
Uma

3

@UmaG will the report be contributed? :thinking:

4

Hi @rmehta,

This is an ERPNext Standard report, in which I had to add the delay days. So, I copied over the report and made my changes. :slight_smile:

5

If it could be useful to someone else, you should send a PR!

6

Sure @rmehta,

I added the field in Ordered Items to be Delivered, which is a Query report. How do I send a PR for this? Will I have to fork the ERPNext and add my changes in the same way as for other reports? I thought it might be easier to just add the field in the Query report, and thatā€™s why I added that additional line of code over here.

7

@rmehta,

I have created a Pull request for this. A first one from me. I hope it goes through. :smiley:

8

If you get feedback, you must implement it and also follow contribution guidelinesā€¦ remember this is a process not a one time thing. All the best!

2 Likes