webERP Forum

Full Version: Purchases from Suppliers - No Data - SOLVED
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Main Menu > Purchases > Purchases from Suppliers produces SQL with this:

WHERE supptrans.trandate>='11/29/2016' AND supptrans.trandate<='11/29/2017'

Apparently the ConvertSQLDate function is not working here so I am looking into it now.

I will post my findings and fix when I figure it out...
The fix was very simple...

Change this:

64: $PeriodFrom = ConvertSQLDate($_POST['PeriodFrom']);
65: $PeriodTo = ConvertSQLDate($_POST['PeriodTo']);

To this:

64: $PeriodFrom = FormatDateForSQL($_POST['PeriodFrom']);
65: $PeriodTo = FormatDateForSQL($_POST['PeriodTo']);

Can one of you check my work and merge this into the trunk?
I can't reproduce this error, but I am using the date format dd/mm/yyyy.
I will test with your mods later today.

Andy.
Thank you Andy.

I have to assume my USA-based locale and date format is causing the issue.
Actually looking at it Paul is correct and the code is wrong, the question is not so much why his doesn't work but why ours with d/m/Y format does work?

Long answer: Looking at the code the answer to the question is in the way the ConvertSQLDate() works. It is meant to be fed Y/m/d format date and if the default format is d/m/Y it reverses the order and gives a Y/m/d format date, but if the default format is m/d/Y it also creates a Y/m/d format date. However if the function is fed an incorrect date (in the format d/m/Y for Andy and m/d/Y for Paul), as happens in this script, it still works for Andy as it simply reverses it to an SQL format, but for Paul it creates an incorrect format.

Short answer: Paul's fix is correct, and if it is applied everyone's code will still work.

Tim
Thank you for having a look at this Tim. I always feel much better when a webERP expert verifies my thoughts and proposed solutions.

Especially when it is you!
I've applied your fix - many thanks Paul
As always thank you both for your help on this project.
Reference URL's