Date / Time format conversions

-- Subject: Date/Time Bug in ODBC API The following 28 date/time formats are supported in Informix: 01 year to year ! 1995 02 year to month ! 1995-01 03 year to day ! 1995-01-01 04 year to hour ! 1995-01-01 01 05 year to minute ! 1995-01-01 01:01 06 year to second ! 1995-01-01 01:01:01 07 year to fraction(5) ! 1995-01-01 01:01:01.00001 08 month to month ! 01 09 month to day ! 01-01 10 month to hour ! 01-01 01 11 month to minute ! 01-01 01:01 12 month to second ! 01-01 01:01:01 13 month to fraction(5) ! 01-01 01:01:01.00001 14 day to day ! 01 15 day to hour ! 01 01 16 day to minute ! 01 01:01 17 day to second ! 01 01:01:01 18 day to fraction(5) ! 01 01:01:01.00001 19 hour to hour ! 01 20 hour to minute ! 01:01 21 hour to second ! 01:01:01 22 hour to fraction(5) ! 01:01:01.00001 23 minute to minute ! 01 24 minute to second ! 01:01 25 minute to fraction(5) ! 01:01.00001 26 second to second ! 01 27 second to fraction(5) ! 01.00001 28 fraction to fraction(5) ! .00001 The values in the above list are the values which were used in the testing of date/time formats in Informix 7.1. Additional note about above formats 04, 05, and 06: When you display these fields directly in the Microsoft table (vs. through a query, form, or report) the data values appear to contain all the right components; however, they are NOT in the format's specified order (i.e., 06 should be "1995-01-01 01:01:01" but appears as "1/1/95 01:01:01 AM"). All of the other Informix date/time types noted in this document as "wrong" appear to have data in them which reflectness the incorrectness of the output. The following seven date & date/time formats are supported in MS Access: 1 General Date ! 1/1/95, 1:01:01 AM 2 Long Date ! Sunday January 1, 1995 3 Medium Date ! 01-Jan-95, or 01 January 1995 ** 4 Short Date ! 1/1/95 5 Long Time ! 1:01:01 AM 6 Medium Time ! 1:01 AM 7 Short Time ! 1:01 The values in the above list are the values which were used in the testing of date/time formats in Microsoft Access 2.0. ** Property list of column 3 shows "Format Medium Date 19 June 1994"; however, Access form displays "01-Jan-95" for our test data. This appears to be a Microsoft Access problem (i.e., not an ODBC problem). The following four Informix date/time formats cause ODBC failure: 01 year to year ! 1995 02 year to month ! 1995-01 08 month to month ! 01 28 fraction to fraction(5) ! .00001 The following list shows the Microsoft Access 2.0 display format for data in the 28 Informix date/time formats when viewed in a report written against a Microsoft Access table which is either "attached" through the current 16 bit ODBC driver, or "imported" through the same driver. Both attached and imported displays are identical. 01 ! none ! ODBC failure 02 ! none ! ODBC failure 03 ! 1/1/95 ! OKAY 04 ! 1:00:00 AM ! wrong format 05 ! 1:01:00 AM ! wrong format 06 ! 1:01:01 AM ! wrong format 07 ! 1:01:01 AM ! wrong format 08 ! none ! ODBC failure 09 ! 1/1/1111 ! wrong format, wrong date 10 ! 1:00:00 AM ! wrong format 11 ! 1:01:00 AM ! wrong format 12 ! 1:01:01 AM ! wrong format 13 ! 1:01:01 AM ! wrong format 14 ! 11/1/1111 ! wrong format, wrong date 15 ! 1:00:00 AM ! wrong format 16 ! 1:01:00 AM ! wrong format 17 ! 1:01:01 AM ! wrong format 18 ! 1:01:01 AM ! wrong format 19 ! 1:00:00 AM ! OKAY 20 ! 1:01:00 AM ! OKAY 21 ! 1:01:01 AM ! OKAY 22 ! 1:01:01 AM ! wrong format 23 ! 11:01:00 AM ! wrong format, wrong date 24 ! 11:01:01 AM ! wrong format, wrong date 25 ! 11:01:01 AM ! wrong format, wrong date 26 ! 11:11:01 AM ! wrong format, wrong date 27 ! 11:11:01 AM ! wrong format, wrong date 28 ! none ! ODBC failure We also went back into the remotely "attached" table, through Microsoft Access, and opened the design screen, and for each field, we manually forced a property "format" to that field of "General Date". After setting this property, we re-ran the reports, with no additional resolution to the problem. It appears that the only intermediate alternative, prior to resolution, is to NOT use date/time formats other than "year to day". It may seem that we should therefore simply use a "date" format; however, we should investigate whether using this date/time format and the "extend" function has any possible advantage for our modeled structures. Has anybody else experienced this problem. Any suggestions?