[OPEN-ILS-DEV] import_demo question

[Dan Scott]

2008/4/29 Nella Lall <ndlall at ucalgary.ca>:
> Thanks for the answer Dan.
>
>  That is good news.  I have finally got the xml file to work properly in
> step 6.  (I had to strip out some nasty 880 tags in our old records before
> everything worked.)  Now, I moved through step 6 and 7 perfectly and then
> bombed out on step 8.
>
>  evergreen-admin at eg-server:~/import_demo$ psql -U evergreen -f
> insert_holdings.sql evergreen 2> insert_holdings.errapr29
>  Password for user evergreen:
>  BEGIN
>  ROLLBACK
>
>  evergreen-admin at eg-server:~/import_demo$ more insert_holdings.errapr29
>  psql:insert_holdings.sql:1: ERROR:  date/time field value out of range:
> "12/13/1993"
>  HINT:  Perhaps you need a different "datestyle" setting.
>  psql:insert_holdings.sql:2: ERROR:  current transaction is aborted,
> commands ignored until end of transaction block
>  psql:insert_holdings.sql:3: ERROR:  current transaction is aborted,
> commands ignored until end of transaction block
>  ......
>
>  So, can you help me figure out how to fix this problem it is having with my
> create date of 12/13/1993.  Out of range?  Too old??  I'm beginning to feel
> too old myself but really, 1993 was not that long ago. :-)

Hi Nella:

Ah - I'm suspecting that we might have our legacy systems set up in a
similar fashion as far as date formats go.

As the error suggests, you could change the PostgreSQL "datestyle"
session variable to temporarily expect dates in the MM/DD/YYYY format
rather than whatever format it currently expects. You seem to have
dates in the "SQL, MDY" format (MM/DD/YYYY), and PostgreSQL (on
Debian, at least) expects dates in the "ISO, MDY" format (MM-DD-YYYY)
by default.

As is documented here
(http://www.postgresql.org/docs/8.1/static/datatype-datetime.html),
you can temporarily change the datestyle setting by adding the
following command as the first line of the insert_holdings.sql file:

SET SESSION datestyle TO SQL, MDY;

Or, if the problem isn't pervasive in your data, or you have a mix of
date styles, you could remove the "BEGIN;" and "COMMIT;" statements
and just let the individual statements with those dates fail if this
is just for testing purposes.

Ultimately, we'll need to beef up the import script to parse the
datetime data and make it consistent with one format.

-- 
Dan Scott
Laurentian University