Thread: Weird PG 8.1.3 date error
Hi, I have a user who is getting this error on a 8.1.3 server when importing some data from a dbf file: PostgreSQL Error Code: (1) ERROR: date/time field value out of range: "08-01-2006" He says the db is SQL-ASCII and the datestyle is at the default. Of course on all my test servers the insert works perfectly without the range error. Anyone have any Ideas? I don't know if the pg server is win32 or Unix. Thanks, -- Tony
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> I have a user who is getting this error on a 8.1.3 server when importing
> some data from a dbf file:
> ERROR: date/time field value out of range: "08-01-2006"
He needs to set DateStyle to match the expected field order.
regression=# show datestyle;
DateStyle
-----------
ISO, MDY
(1 row)
regression=# select '08-01-2006'::date;
date
------------
2006-08-01
(1 row)
regression=# set datestyle TO ymd;
SET
regression=# select '08-01-2006'::date;
ERROR: date/time field value out of range: "08-01-2006"
HINT: Perhaps you need a different "datestyle" setting.
regression=#
He might also want to use tools that don't suppress the HINT part of an
error message ...
regards, tom lane
On Wed, 2006-12-13 at 10:37 -0600, Tony Caduto wrote: > Hi, > I have a user who is getting this error on a 8.1.3 server when importing > some data from a dbf file: > > PostgreSQL Error Code: (1) > ERROR: date/time field value out of range: "08-01-2006" > > He says the db is SQL-ASCII and the datestyle is at the default. Without more information it is a bit tough to say but: http://people.planetpostgresql.org/joshua/index.php?/archives/12-Ahhh!-PostgreSQL-Gotchas.html > > Of course on all my test servers the insert works perfectly without the > range error. > > Anyone have any Ideas? I don't know if the pg server is win32 or Unix. > > Thanks, > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
Tom Lane wrote: > He needs to set DateStyle to match the expected field order. > > regression=# show datestyle; > DateStyle > ----------- > ISO, MDY > (1 row) > > regression=# select '08-01-2006'::date; > date > ------------ > 2006-08-01 > (1 row) > > regression=# set datestyle TO ymd; > SET > regression=# select '08-01-2006'::date; > ERROR: date/time field value out of range: "08-01-2006" > HINT: Perhaps you need a different "datestyle" setting. > regression=# > > Tom, You were right on, that's exactly what the problem was. I have not heard back, but I told him to set his datestyle back to the default. Thanks, -- Tony