Thread: Date in JDBC

I am first time using the date type in the jdbc or potgreSQL in general. One field of a DB table is the date type. In a

query string I have:

INSERT INTO account(firstname, status, userid,addr1,addr2,city,province,postcode,email,phone, cellphone, rdate,
notes, lastname) VALUES ('John', 'n', 'rt', ' 340 Road', 'suite 201', 'mycity', 'wa', '98034', '34@32.com',
'999-888-2222', 
'111-222-3333', 'Mon Jun 10 08:42:06 SGT 2002', 'NA', 'familyname')

And I get the error message java.sql.SQLException: ERROR:  Bad date external representation 'Mon Jun 10 08:4
2:06 SGT 2002'.

I use the java.util.date to create a new date instance to convert to string. What is the right way to get the date
right?

Thanks in advance.

Vernon

On Monday, June 10, 2002, at 08:39 PM, Vernon Wu wrote:
>
> I am first time using the date type in the jdbc or potgreSQL in
> general. One field of a DB table is the date type. In a
> query string I have:
>
> INSERT INTO account(firstname, status,
> userid,addr1,addr2,city,province,postcode,email,phone, cellphone, rdate,
> notes, lastname) VALUES ('John', 'n', 'rt', ' 340 Road', 'suite 201',
> 'mycity', 'wa', '98034', '34@32.com', '999-888-2222',
> '111-222-3333', 'Mon Jun 10 08:42:06 SGT 2002', 'NA', 'familyname')
>
> And I get the error message java.sql.SQLException: ERROR:  Bad date
> external representation 'Mon Jun 10 08:4
> 2:06 SGT 2002'.
>
> I use the java.util.date to create a new date instance to convert to
> string. What is the right way to get the date right?

I think you can only use that format with the timestamp type.  For the
plain old date type,
I always use something like mm-dd-yyyy (or however you have formatted
your date).
To get the date in that format, I wrote the following function:

public String getDateString(Date d)
{
     Calendar cal = new GregorianCalendar();
     cal.setTime(d);
     int month = cal.get(Calendar.MONTH) + 1;
     int day = cal.get(Calendar.DAY_OF_MONTH);
     int year = cal.get(Calendar.YEAR);
     return month + "-" + day + "-" + year;
}

HTH,
--Jeremy

A far better way is to use prepared statements and setDate

Dave
On Tue, 2002-06-11 at 16:23, Jeremy Buchmann wrote:
> On Monday, June 10, 2002, at 08:39 PM, Vernon Wu wrote:
> >
> > I am first time using the date type in the jdbc or potgreSQL in
> > general. One field of a DB table is the date type. In a
> > query string I have:
> >
> > INSERT INTO account(firstname, status,
> > userid,addr1,addr2,city,province,postcode,email,phone, cellphone, rdate,
> > notes, lastname) VALUES ('John', 'n', 'rt', ' 340 Road', 'suite 201',
> > 'mycity', 'wa', '98034', '34@32.com', '999-888-2222',
> > '111-222-3333', 'Mon Jun 10 08:42:06 SGT 2002', 'NA', 'familyname')
> >
> > And I get the error message java.sql.SQLException: ERROR:  Bad date
> > external representation 'Mon Jun 10 08:4
> > 2:06 SGT 2002'.
> >
> > I use the java.util.date to create a new date instance to convert to
> > string. What is the right way to get the date right?
>
> I think you can only use that format with the timestamp type.  For the
> plain old date type,
> I always use something like mm-dd-yyyy (or however you have formatted
> your date).
> To get the date in that format, I wrote the following function:
>
> public String getDateString(Date d)
> {
>      Calendar cal = new GregorianCalendar();
>      cal.setTime(d);
>      int month = cal.get(Calendar.MONTH) + 1;
>      int day = cal.get(Calendar.DAY_OF_MONTH);
>      int year = cal.get(Calendar.YEAR);
>      return month + "-" + day + "-" + year;
> }
>
> HTH,
> --Jeremy
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>