PxPlus User Forum
Main Board => Discussions => ODBC => Topic started by: Mike Hatfield 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
-
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'
-
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