Re: 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.
>