Thread: Re: Date Problem on Imports to 7.2.1 [ SOLVED ]

Re: Date Problem on Imports to 7.2.1 [ SOLVED ]

From
"Geoff Ellis"
Date:
This was over looked.

3.5.1. Date/Time Input
Date and time input is accepted in almost any reasonable format, including
ISO 8601, SQL-compatible, traditional PostgreSQL, and others. For some
formats, ordering of month and day in date input can be ambiguous and there
is support for specifying the expected ordering of these fields. The command
SET DateStyle TO 'US' or SET DateStyle TO 'NonEuropean' specifies the
variant "month before day", the command SET DateStyle TO 'European' sets the
variant "day before month". The ISO style is the default but this default
can be changed at compile time or at run time.



thanks.

Geoff

-----Original Message-----
From: Geoff Ellis [mailto:geoff@metalogicplc.com]
Sent: 27 August 2003 13:38
To: 'Pgsql-Admin (E-mail)'
Subject: Date Problem on Imports to 7.2.1


We have some date problems when importing data into the PSQL system.  The
following example shows it happening:

Source file = spec.csv
2,1,08/06/99,29/07/99,1,1,A,'R' CONDITION,C,MA,,O,,,,,,,,,,WS000

When imported into Postgres, the data is shown as:
mss_live=# select id,cdate,mdate,code,type,category from spec_header limit 1
;
 id |         cdate          |         mdate          |     code      | type
| category
----+------------------------+------------------------+---------------+-----
-+----------
  2 | 1999-08-06 00:00:00+01 | 1999-07-29 00:00:00+01 | 'R' CONDITION | C
| MA
(1 row)

The data was imported with "copy table from 'path/to/file' delimiters ','
with '' as null.

As you can see, 08/06/99 (8th June 1999) has been converted to 1999-08-06
(6th August 1999), and 29/07/99 (29th July 1999) has been converted
correctly!

Is there a flag or setting within the database that will allow us to import
the date correctly?

thanks in anticipation..

Geoff Ellis