Re: Incorrect import of timestamp fields in MS Access with7.2.x

Edwin Woudt wrote:
>
> Hiroshi Inoue <Inoue@tpf.co.jp> wrote:
>
> >> After switching from PostgreSQL 7.1.3 to 7.2, I'm having trouble with
> >> timestamp fields in ODBC-linked tables in MS Access (both '97 and XP).
> >>
> >> The problem is that MS Access somehow thinks these are 'Text' fields
> >> instead of 'Date/Time' fields. It worked fine when using the 7.1.3
> >> server, but not with 7.2 and 7.2.1.
> >
> > What kind of problem do you have concretely ?
>
> Apologies if I was not clear.
>
> I want MS Access to recognize the timestamp field as a Date/Time field and
> not as a Text field. This has some advantages as access can then handle
> formatting and input itself.
>
> I did some more debugging and found in de psqlodbc log file that in 7.1.3
> the field has type 1184 (PG_TYPE_DATETIME), while in 7.2.x the field has
> type 1114 (PG_TYPE_TIMESTAMP_NO_TMZONE).

Oh I see. I would fix it.

> --- pgtypes.c.orig      Tue Apr 16 13:06:36 2002
> +++ pgtypes.c   Tue Apr 16 13:08:25 2002
> @@ -311,10 +311,11 @@
>  #endif /* ODBCVER */
>                         return SQL_TIME;
>                 case PG_TYPE_ABSTIME:
>                 case PG_TYPE_DATETIME:
>                 case PG_TYPE_TIMESTAMP:
> +               case PG_TYPE_TIMESTAMP_NO_TMZONE:
>  #if (ODBCVER >= 0x0300)
>                         if (EN_is_odbc3(env))
>                                 return SQL_TYPE_TIMESTAMP;
>  #endif /* ODBCVER */
>                         return SQL_TIMESTAMP;

I'm suspicious if the above is sufficeient.

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/