Re: [INTERFACES] Access'97 and ODBC - Mailing list pgsql-interfaces

On Tue, 28 Apr 1998, Byron Nikolaidis wrote:

>
>
> 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.

Why not ISO-8601 this is the Standard SQL92 date format (i.e. YYYY-MM-DD)
and for coherence with PostgreSQL User's Guide, quoting Thomas Lockhart
at page 14, chapter 4, under "Date/Time Styles":

   "For Postgres v6.3 (and earlier) the default date/time style is
   "traditional Postgres". In future releases, the default may become
   ISO-8601, which alleviates date specification ambiguities and Y2K
   collation problems."

I vote for changing default date format to ISO-8601 to reflect PostgreSQL
documentation and for adherence to Standard SQL92.
                                                           Jose'