Post Reply 
 
Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Purchases from Suppliers - No Data - SOLVED
11-29-2017, 09:48 PM (This post was last modified: 11-29-2017 10:35 PM by VortecCPI.)
Post: #1
Purchases from Suppliers - No Data - SOLVED
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?

https://www.linkedin.com/in/eclipsepaulbecker
Visit this user's website Find all posts by this user
Quote this message in a reply
11-29-2017, 10:01 PM (This post was last modified: 11-29-2017 10:02 PM by afcouling.)
Post: #2
RE: Purchases from Suppliers - Date Format Issue
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.

https://www.linkedin.com/in/andrewcouling
Find all posts by this user
Quote this message in a reply
11-29-2017, 10:37 PM
Post: #3
RE: Purchases from Suppliers - No Data - SOLVED
Thank you Andy.

I have to assume my USA-based locale and date format is causing the issue.

https://www.linkedin.com/in/eclipsepaulbecker
Visit this user's website Find all posts by this user
Quote this message in a reply
11-29-2017, 11:26 PM
Post: #4
RE: Purchases from Suppliers - No Data - SOLVED
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
Visit this user's website Find all posts by this user
Quote this message in a reply
11-29-2017, 11:54 PM
Post: #5
RE: Purchases from Suppliers - No Data - SOLVED
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!

https://www.linkedin.com/in/eclipsepaulbecker
Visit this user's website Find all posts by this user
Quote this message in a reply
12-02-2017, 05:37 PM
Post: #6
RE: Purchases from Suppliers - No Data - SOLVED
I've applied your fix - many thanks Paul

Phil Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
Visit this user's website Find all posts by this user
Quote this message in a reply
12-03-2017, 12:19 AM
Post: #7
RE: Purchases from Suppliers - No Data - SOLVED
As always thank you both for your help on this project.

https://www.linkedin.com/in/eclipsepaulbecker
Visit this user's website Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump:


User(s) browsing this thread: 1 Guest(s)