Thread: Date format problem with INSERT statement.

Date format problem with INSERT statement.

From
Olav Gjerde
Date:
If I do an:

INSERT INTO wdata (date)
VALUES ('13.04.06');

From the Query Tool in PGAdmin it will insert the dd.mm.yy format correctly without problems.


But when I do this in Java I get this error message

SQLException: ERROR: date/time field value out of range: "13.04.06"
SQLState:    22008
VendorError:  0

This is because it reads the date format as mm.dd.yy because when I insert with the value '12.04.06' it works perfect. I've changed the datestyle in my Database to European but it still doesn't work. Do the JDBC driver only insert dates in the US format?

Olav

Re: Date format problem with INSERT statement.

From
"Xavier Poinsard"
Date:
You should consider using the jdbc escape format for dates {d
'yyyy-mm-dd'}  =>
INSERT INTO wdata (date)  VALUES ({d '2006-04-13');

see http://jdbc.postgresql.org/documentation/head/escapes-datetime.html


Olav Gjerde a écrit :
> If I do an:
>
> INSERT INTO wdata (date)
> VALUES ('13.04.06');
>
> From the Query Tool in PGAdmin it will insert the dd.mm.yy format
> correctly without problems.
>
>
> But when I do this in Java I get this error message
>
> SQLException: ERROR: date/time field value out of range: "13.04.06"
> SQLState:    22008
> VendorError:  0
>
> This is because it reads the date format as mm.dd.yy because when I
> insert with the value '12.04.06' it works perfect. I've changed the
> datestyle in my Database to European but it still doesn't work. Do the
> JDBC driver only insert dates in the US format?
>
> Olav


Re: Date format problem with INSERT statement.

From
"Guy Rouillier"
Date:
Olav Gjerde wrote:
> But when I do this in Java I get this error message
>
> SQLException: ERROR: date/time field value out of range: "13.04.06"
> SQLState:    22008
> VendorError:  0
>
> This is because it reads the date format as mm.dd.yy because when I
> insert with the value '12.04.06' it works perfect. I've changed the
> datestyle in my Database to European but it still doesn't work. Do
> the JDBC driver only insert dates in the US format?

Consider using PreparedStatement.  Then you can store your date value as
a java.sql.Date variable, and set the column value to that.

--
Guy Rouillier