Re: Access 97 and Informix ODBC

Edward Villalovoz wrote: > I have an odbc connection between access 97 and informix 7.x on solaris. > When I run a pass through query on one of the fields in informix that is > set to "datetime hour to min", i don't get the "12:00" that i expect. I > get "11/11/99 12:00:00 PM". The field only contains "12:00". If I link the > table through the odbc driver I get the same result when I view the data. > If I change the format on the the field in the linked table to "short time" > then I get the results I want, but I can't run my pass through query. I am > currently running the last Informix SDK, 2.02?. I've tried other odbc > drivers, Intersolv 3.11 and OpenLink. Anyone have a cool work around or > fix? Thanks in advance.... Edward Typically you may find that many different RDBMSs represent the notion of date and time in varying datatypes. ODBC attempts to rationalise these to a useful generic set in the context of ODBC. An ODBC driver has to take the RDBMS datatype and convert it to an ODBC SQL datatype (various rules of conversion apply: see the ODBC standard). The ODBC SQL datatype to convert to should be chosen to best match the storage capacity of the RDBMS datatype. If we take, for instance, an Informix 'datetime hour to minute', perhaps two sensible choices present themselves: these are SQL_TIME and SQL_TIMESTAMP. From the description, it seems that the ODBC drivers that you mention may have opted for the SQL_TIMESTAMP mapping. This mapping while perhaps valid is not as precise as it may be. The better option might be SQL_TIME. If we take the example of 12:00, if it was mapped to SQL_TIMESTAMP it may appear as a full datetime representation (because it is!) e.g. 1998-08-25 12:00:00. SQL_TIME, by contrast, allows a more precise representation of 'datetime hour to minute' in that it has the hour and minute component. It also has a seconds component which you may see as a suffixed :00 (ODBC standard, so I guess you get the seconds even if there aren't any). SQL-Retriever for Informix maps datetime (hour to second) to SQL_TIME, and therefore 'datetime hour to minute' would show 12:00:00 if 12:00 is stored in the RDBMS. To further complicate matters, SQL datatypes are mapped to C datatypes (requested by the application). This mapping can further distort the representation of the data. An optimum C mapping for SQL_TIME is SQL_C_TIME. If the application chooses SQL_C_TIMESTAMP then the SQL_TIME will become a timestamp (with certain conversion rules applied). You may also be able to get your application (Access) to re-format the time as well, as you have stated. Our ODBC driver, SQL-Retriever will transfer your Informix 'datetime hour to minute' to hh:mm:ss in Access (and you state that you will then be able to reduce it further in Access). Take a look at http://www.sco.com/vision/products/sqlretriever/ for more information and a downloadable eval. Allan Gould (allang at sco dot com) (Please remove anti-spam measures if replying)