Providex ODBC Invalid Date - Corrupt Date?

SOLVED

Have an issue with what I believe is a corrupted date value.  It resides in purchase order detail required date and PO header PO date.

Running the open order report on "required expire date" for date greater than 12/30 will result in "Vendor Code 9" database error. Running a similar query in the ODBC in excel results in:

[Providex][ODBC Driver][FILEIO]Invalid Date Contents

Now, if you break the query into a range between 12/30/2015 and 12/31/2015 and another one for greater than 1/1/2016 information will return. So I suspect that there is a date somewhere with a garbled day in december, but I'm not sure, or how to locate it if this is actually the case.

Anyone seen anything like this?

Thanks in advance!

  • 0
    verified answer
    Print the report in purchase order number order. Keep bracketing (printing ranges) in until you find the exact purchase order that causes the problem. Once you have identified the specific purchase order, try going in to purchase order entry and bringing up that purchase order. Look for any date field that does not appear to be formatted properly.

    If that does not work, you will have to use data file display and maintenance to bring up the records for that purchase order on purchase order header or purchase order detail. Review for dates that are not formatted properly and fix it. A valid format will look something like "05012015". A bad date will usually look like "05 012015". (note I'm not sure of the exact format off the top of my head, just compare against other records).

    If you are unfamiliar with data file display and maintenance, I recommend you call your Sage Partner. Not a bad idea to backup up first either. You can do significant damage if you do not know what you are doing.
  • 0 in reply to TomTarget
    Thanks, this helped us find the offender with relative ease.
  • 0
    A method we now use to locate a bad date value (helpful when the table has 500,000+ records and not sure which field is producing the error) is to use VI and export the key field and all the fields containing dates to a CSV file from the problem table. Then we use Microsoft Access to import the CSV file to a new table in Access. Any bad dates will produce a Type Conversion error during the import with a row number where the bad record is. Using notepad++ (or similar editor with line numbers), open the CSV file and go to the row that produced the error to determine the key value and which field has the invalid date. Using DFDM, search for the key value and edit the bad record(s). Remember to backup your table before performing any edits!