Re: Bad External Date Representation error for any date which

Hale,

I never said a statement object wouldn't work, I just wanted to point
the person to a better way of doing things.  In general if you have
values that need to be supplied to a SQL statement, you should use a
PreparedStatement.

If someone is using a regular statement object and building up the SQL
statement themselves they need to be aware of the formats that will and
will not work when it comes to dates.

If the "standard escape format" was used that would have worked.  But
that isn't the format that was indicated was being used in the original
message.

--Barry

Hale Pringle wrote:
> Barry,
> Your answer concerns me (the first one in over two years).  The PostgreSQL
> JDBC documentation indicates that a statement should work as well as a
> prepared statement and this seems reasonable.
>
> I set up a test table using the create statement below. My PostgreSQL is
> 7.2.1 and JDBC is dated 2/9/2002.  Inserts and updates worked fine.
>
> In my research of I18N, they talked about all JDBC drivers supporting the
> "standard escape format" which is '2002-11-04' so one option is use Java's
> date formatting routines to convert to this format.
>
> I think we should track further to find out why is doesn't work in certain
> situations.
>
> TIA
> Hale
>
>
>
>>-----Original Message-----
>>From: pgsql-jdbc-owner@postgresql.org
>>[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Barry Lind
>>Sent: Wednesday, June 04, 2003 8:33 PM
>>To: dean walliss
>>Cc: pgsql-jdbc@postgresql.org
>>Subject: Re: [JDBC] Bad External Date Representation error for any date
>>which
>>
>>
>>Dean,
>>
>>What I think you really want to be using is a PreparedStatement.
>>
>>   PreparedStatement pstmt = conn.prepareStatement("insert into memo
>>(memodate, memotext, lastmod) values (?,?,?)");
>>   pstmt.setDate(1, myDate);
>>   pstmt.setString(2, myString);
>>   pstmt.setTimestamp(3, myTimestamp);
>>   ResultSet rset = pstmt.executeQuery();
>>   ...
>>
>>Using a PreparedStatement lets the driver take care of the work
>>necessary to send the value to the server in the correct format, so that
>>you don't need to worry about it.
>>
>>thanks,
>>--Barry
>>
>>
>>dean walliss wrote:
>>
>>>Hi,
>>>
>>>I'm getting a "Bad External Date Representation" error when I try to
>>>insert a record containing a date which falls on a Saturday.
>>>
>>>I'm using postgresql v 7.3.3
>>>
>>>I'm using the jdbc driver pg73jdbc3.jar build110 from 28/05/2003
>>>
>>>The example table looks like this:
>>>
>>>CREATE TABLE Memo (
>>>   MemoId SERIAL NOT NULL,
>>>   MemoDate DATE NOT NULL,
>>>   MemoText TEXT,
>>>   LastMod TIMESTAMP,
>>>  PRIMARY KEY (MemoId)
>>>);
>>>
>>>The query looks like this:
>>>
>>>insert into Memo (MemoDate, MemoText, LastMod)
>>>values ('Sat Nov 02 00:00:00 EST 2002', 'Something should
>>
>>happen on this
>>
>>>day.', '2003-06-02 15:45:44.665');
>>>
>>>
>>>The error looks like this:
>>>
>>>ERROR:  Bad date external representation 'Sat Nov 02 00:00:00 EST 2002'
>>>
>>>Note that if I change the "Sat" portion to "Sun", the query executes
>>>properly, even though Nov 02 2002 is not a Sunday.
>>>
>>>Note that if I remove the three-character day representation altogether
>>>and use "Nov 02 00:00:00 EST 2002", the query executes properly.
>>>
>>>Note that this representation of the date "Sat Nov 02 00:00:00
>>
>>EST 2002"
>>
>>>results from using a java.sql.Date object.
>>>
>>>Is there something about the Date datatype in postgres that I'm
>>>missing?  Is there some idiomatic way to convert a java.sql.Date object
>>>into something that postgres (or it's jdbc driver) can use?
>>>
>>>Thanks.
>>>
>>>Dean.
>>>
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to majordomo@postgresql.org so that your
>>message can get through to the mailing list cleanly
>
>
>