PxPlus User Forum

Twitter Twitter Twitter

Author Topic: ODBC Date Issue  (Read 1035 times)

Jeffrey Ferreira

  • Diamond Member
  • *****
  • Posts: 175
  • I must have taken a wrong turn at Albuquerque.
    • View Profile
ODBC Date Issue
« on: March 03, 2023, 08:14:56 AM »
Hi All,

We are using ODBC Driver 7.00.
If we do the following in Excel

Select Order_Number, Order_Date from "Open Orders" WHERE  Order Date > "09/01/2022"

it returns every order up until 12/31/2022 and does not return anything from 2023

if  we do

Select Order_Number, Order_Date from "Open Orders" WHERE  Order Date > "01/01/2023"

then it returns the 2023 orders

has anyone ever seen this or know what i could be doing wrong?

thanks
jeff


Devon Austen

  • Administrator
  • Diamond Member
  • *****
  • Posts: 382
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: ODBC Date Issue
« Reply #1 on: March 03, 2023, 09:14:08 AM »
I can see a few possible issues:

1. your WHERE clause is not using the correct field name. It should be Order_Date not Order Date. You need the underscore and not a space. Then again maybe that is just an issue with typing it out for the forum but it is in both your examples so it could be the issue.

2. Use single quote not double quote.

2. If Order_date is defined with a DATE class you may need to use the date format yyyy-mm-dd in your SQL query.

WHERE Order_Date > '2022-09-01'
Principal Software Engineer for PVX Plus Technologies LTD.

Jeffrey Ferreira

  • Diamond Member
  • *****
  • Posts: 175
  • I must have taken a wrong turn at Albuquerque.
    • View Profile
Re: ODBC Date Issue
« Reply #2 on: March 03, 2023, 09:20:19 AM »
Devon,

i'm sorry i was just pseudo coding it quick to give you all an idea what i was trying to do

i'm not creating the SQL Select (Excel is).


jeff

Jeffrey Ferreira

  • Diamond Member
  • *****
  • Posts: 175
  • I must have taken a wrong turn at Albuquerque.
    • View Profile
Re: ODBC Date Issue
« Reply #3 on: March 03, 2023, 11:50:43 AM »
I just tried it with Excel Power Query and that failed too.  I tested it against another database we have (mysql) and that worked. Is it possible there is something wrong with the 7.00 ODBC Driver. This worked when we were using 4.21.

jeff

Devon Austen

  • Administrator
  • Diamond Member
  • *****
  • Posts: 382
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: ODBC Date Issue
« Reply #4 on: March 03, 2023, 02:18:44 PM »
I am able to filter based on a date field in Excel just fine.

As I said earlier the issue may be the date format you are trying to use. The ODBC expects YYYY-MM-DD for date fields.
Principal Software Engineer for PVX Plus Technologies LTD.

Jeffrey Ferreira

  • Diamond Member
  • *****
  • Posts: 175
  • I must have taken a wrong turn at Albuquerque.
    • View Profile
Re: ODBC Date Issue
« Reply #5 on: March 03, 2023, 02:25:02 PM »
Devon can i bug you with one last question...

when you say ODBC  expects YYYY/MM/DD are you saying that my date needs to be stored in the pxplus Data Dictionary as YYYY/MM/DD -> because our dates are stored as MM/DD/YY in the pxplus data dictionary.

or are you just saying the WHERE clause needs to be in 'YYYY/MM/DD' format.

jeff




Mike King

  • Diamond Member
  • *****
  • Posts: 3810
  • Mike King
    • View Profile
    • BBSysco Consulting
Re: ODBC Date Issue
« Reply #6 on: March 03, 2023, 02:34:25 PM »
Jeff

A couple of things you might try (and why I am suggesting them):

1) If there is no WHERE clause does it return all the orders.  (Checking all records accessible and none have invalid data/keys)

2) Do you have a key on the file based on the date and is it Ascending or Descending? (Perhaps the driver is using an alternate key to access the file and the definition/data is out of sequence)

3) If you add an ORDER BY on the ORDER_NUMBER does the query work? (Try to force the system to use the primary key)

4) What is the CLASS for the Date field in the PxPlus Data Dictionary? (could the class name be incorrect given the data in the record -- if record has dates stored as MMDDYY then the class should be DATE-MMDDYY.
Mike King
President - BBSysco Consulting
eMail: mike.king@bbsysco.com

Jeffrey Ferreira

  • Diamond Member
  • *****
  • Posts: 175
  • I must have taken a wrong turn at Albuquerque.
    • View Profile
Re: ODBC Date Issue
« Reply #7 on: March 03, 2023, 02:52:22 PM »
Hi Mike

1.) it returns all the data
2.) i did have a key so i tried the order by and it did not work....
     but to be sure i tried on another date field (no key) ...stilll did not work
3.) Order by primary key (no date in key) - did not work
4.) I think the data class might be the problem...

i'm going to take this off line...thanks for the info..

Devon Austen

  • Administrator
  • Diamond Member
  • *****
  • Posts: 382
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: ODBC Date Issue
« Reply #8 on: March 03, 2023, 03:26:31 PM »
To answer your question I am only referring to the WHERE clause.

and the format is YYYY-MM-DD not YYYY/MM/DD dashes and not forward slashes.
Principal Software Engineer for PVX Plus Technologies LTD.

Jeffrey Ferreira

  • Diamond Member
  • *****
  • Posts: 175
  • I must have taken a wrong turn at Albuquerque.
    • View Profile
Re: ODBC Date Issue
« Reply #9 on: March 03, 2023, 03:47:08 PM »
So I just spoke to our Product Manager. Our dates in this file use the format:

DATE-MM/DD/AA

the AA was a Legacy Y2K Data Class. Could that be the issue?

jeff

Devon Austen

  • Administrator
  • Diamond Member
  • *****
  • Posts: 382
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: ODBC Date Issue
« Reply #10 on: March 06, 2023, 09:28:23 AM »
The PxPlus SQL ODBC driver supports that date class.

When using SQL though you use the SQL date format (YYYY-MM-DD) the driver does the conversion to how the date is stored in your file. So assuming your data dictionary has set the date class for the field correctly if you do a SQL query like

SELECT * FROM myTable WHERE datefld > '2022-09-01'

you should get what you want.
Principal Software Engineer for PVX Plus Technologies LTD.