Main Board > ODBC

Dictionary definition for dates in YYMMDD format

(1/1)

Mike Hatfield:
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

Devon Austen:
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'

Mike Hatfield:
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

Navigation

[0] Message Index