Thread: Bad External Date Representation error for any date which is a Saturday.

"dean walliss" <daw700@netspace.net.au> writes:
> ERROR:  Bad date external representation 'Sat Nov 02 00:00:00 EST 2002'

Might you have AUSTRALIAN_TIMEZONES set true?  "SAT" appears to be
considered a timezone abbreviation when that's the case --- this is
probably confusing the code that would otherwise be willing to take it
as a noise word.

            regards, tom lane

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

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