PxPlus User Forum

Twitter Twitter Twitter

Author Topic: Dictionary definition for dates in YYMMDD format  (Read 922 times)

Mike Hatfield

  • Gold Member
  • ****
  • Posts: 70
    • View Profile
Dictionary definition for dates in YYMMDD format
« on: March 06, 2023, 01:23:03 AM »
We are working on a legacy system that has dates stored in the format YYMMDD.
We are trying to use ODBC with Excel to extract from the file with a criterium based on date EG select dates <= 30/06/22
What is the correct definition in the data Dictionary so that you can select by date in ODBC (EG 30/06/22).
Would we create a Date Class and apply that to the element and if so, how?

Thanks
Mike Hatfield
Mike H

Devon Austen

  • Administrator
  • Diamond Member
  • *****
  • Posts: 382
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: Dictionary definition for dates in YYMMDD format
« Reply #1 on: March 06, 2023, 09:42:40 AM »
You can define the class as DATE-YYMMDD

https://manual.pvxplus.com/?odbc/table_definitions/classes.htm

This will tell the ODBC driver that the field is a date. From excel you can just ignore the format the date is stored in and use the standard SQL date format YYYY-MM-DD in your SQL query.

So if using the query wizard to get data you can just define a filter for that field <= 2022-06-30.

Or if you use the Microsoft Query you can edit the SQL query directly it would be something like

SELECT * FROM myTable WHERE dateFld <= '2022-06-30'
Principal Software Engineer for PVX Plus Technologies LTD.

Mike Hatfield

  • Gold Member
  • ****
  • Posts: 70
    • View Profile
Re: Dictionary definition for dates in YYMMDD format
« Reply #2 on: March 06, 2023, 04:51:17 PM »
Hi Devon,
I defined the class DATE-YYMMDD and it works. So simple and so obvious!
I have been trying all sorts of combinations around DATE-AAMMDD thinking that would be the right way to go.
Thinking about that I remember back to the Y2K 'bug' that this is specific to that point in time.
Thanks for the reply.

Follow-up question:
for the Class 'DATE' the element is auto-filled with 00/00/0000 and FMT=DD/MM/YYYY,DAYFMT=YYYYMMDD,INP=00/00/0000
for the Class 'DATE-YYMMDD' would an equivalent be 00/00/00 and FMT=DD/MM/YY,DAYFMT=YYMMDD,INP=00/00/00

Thanks again
Mike H