Re: [INTERFACES] Access'97 and ODBC

Hannu Krosing wrote:

> Jose' Soares Da Silva wrote:
> >
> > I have a table with field1 DATE and field2 TIMESTAMP. If I insert data
> > into these fields, field2 looks OK, but Access97 show me a strange
> > date on field1.
> >
> > This is Access97 output:
> >                  field1: 27/7/99
> >                  field2: 1998-04-27 12:20:21+02
> >
> > This is psql output:
> >                  Field | Value
> >                  -- RECORD 0 --
> >                  field1| 1998-04-27
> >                  field2| 1998-04-27 12:20:21+02
> > ----
> > PS: My DateStyle is setting to 'ISO'
>
> You should set it to 'US' when using Insight ODBC drivers.
>
> It should affect the output in no way, but the driver expects it from
> the backend in US format. As this is a per-connection setting it can
> safely be set from the driver at startup without affecting other
> connections.
>
> There has been some discussion about 'fixing' it and making the
> driver recognize other date formats. That would be IMHO unnecessary.
> It should be enough just to do "SET DateStyle TO 'US';" at startup.
>
> This can be currently done by setting some registry entries, but
> this should really be just a part of driver startup.
>
> Hannu


Hannu,

I understand what you are saying here, and am very tempted to just go with
setting the datestyle to US at connection time by default.  It is true that
this would have no negative effect on applications such as Access.

But, before I do, is there cases out there where people are executing DIRECT
queries through the driver where they are expecting the date to be in a
particular format such as:

insert into tablex (date1) values('28-04-1998')                #  DD-MM-YYYY
format

If the driver always sets the datestyle to "US", the above insert might not
work.  Of course, I would imagine the query should be written more portably
using the ODBC shorthand escape syntax, as:

insert into tablex (date1) values( {d '1998-04-28'} ),

which would work correctly.  The reverse is true also, if the user does
"select date1 from tablex", and uses SQL_C_CHAR as the return type,
expecting the format to be EURO, when in fact it would be US.

If no one has any objections, I will change the driver to always set the
datestyle to US, and forget about adding a selection to the dialogs to
select it.

Byron