Re: timestamp parse error --- *** Migration PLU

> -----Original Message-----
> From: Samuele Brignoli [mailto:samuele(dot)brignoli(at)darpha(dot)com]
> Sent: 21 March 2002 08:21
> To: Dave Page; pgadmin-support(at)postgresql(dot)org
> Subject: R: [pgadmin-support] timestamp parse error --- ***
> Migration PLUG-IN ***
>
>
> Yes, but the problem is with database migration plugin.

Yes, that is what I'm referring to when I say pgAdmin. I look at the whole
thing.

> It
> seems that this component extract data from sql server to the
> format 00.00.00 which is not correct.

The Migration Wizard uses the settings of your ODBC datasource/Access MDB
file/SQL Server OLEDB datasource, which in turn probably uses your system's
locale settings.

> This tool ( I don' t
> know why and how ) try, at the end, to insert the timestamp
> as 00.00.00 which generates an error.

It uses a Microsoft function to convert the date from adDBTimestamp (that's
a Microsoft ADO data type) to ISO 8601 format (yyyy-MM-dd hh:mm:ss) which is
recognised by PostgreSQL *no matter what the locale*. The only way I can see
that it doesn't do this conversion, is if Microsoft's ADOX does not
recognise the source column as being adDBTimestamp, in which case (with a
few exceptions) the Migration Wizard will not change it in any way, and will
insert the data as it was supplied by the souce database.

> I' ve talked to
> postgresql specialist that told me that this dot notation (
> 00.00.00 ) is not supported by postgresql.

Yes, I told you that some time ago.

> Where does it come
> from ? I don' t think It's a particular configuration of my
> sql server.
>
> Surely a simple check in the Migration plug-in, which manage
> this conversion, can solve the problem.

There already is one. From the CVS:
(http://cvs.pgadmin.org/cgi-bin/viewcvs.cgi/pgadmin2/plugins/migration/frmWi
zard.frm?rev=1.8&content-type=text/vnd.viewcvs-markup&logsort=rev)

'We used to have a bit of a hack here, but if the recordset thinks it has a
valid date
'then it must do. Format to ISO (8601? - I can never remember!) will
automatically add
'1899-12-30 if only a time is actually in the string
Case adDate, adDBDate, adDBTimeStamp
Values = Values & "'" & Format(rsTemp.Fields(Z).Value, "yyyy-MM-dd
hh:mm:ss") & "', "

Note that this is from the version of the file that pgAdmin 1.2.0 was built
with, not a current fixed version.

Regards, Dave.