db2 year 2000 invalid date

[arunij katiliyar]

Hi ,

I am encountering the following error when inserting 2000 as the value for the year in a date field in DB2 8.1 .

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0113E SQLSTATE 22007: An invalid datetime format was detected; that is, an invalid string representation or value was specified. SQLSTATE=22007

May i know why is this happening

Thanks
Arun

[Jamie Robertson]

usually this error occurs if the date is sent to the DB as a string that is in a format not compatible with the DB. Make sure that the DB can parse the format that you send it.

i.e. if the DB accepts YYYY-MM-DD and you send it in the format MMM-DD-YYYY or any other unacceptable format.

Jamie

[Jamie Robertson]

and if I remember correctly, DB2 by default can automatically convert MM-DD-YYYY and YYYY-MM-DD.

[arunij katiliyar]

Hi ,

I am sending the date format as follows

String tempDate="2000-02-08";

PreparedStatement pstmt = connection.prepareStatement("INSERT QUERY");

pstmt.setString(1,tempDate)

The problem is for only the dates that has 2000 in its year .


One more fact ,

when i issue a select SQL from db2 client for the column it displays in

the format

mm/dd/yyyy

Thanks
Arun

[Shailesh Chandra]

Originally posted by arunij katiliyar:

pstmt.setString(1,tempDate)

Why are you not sending it as pstmt.setDate(1,tempDate_as_date_object);

any special reason ??

Shailesh

[Adeel Ansari]

Originally posted by arunij katiliyar:
One more fact ,

when i issue a select SQL from db2 client for the column it displays in

the format

mm/dd/yyyy

Answer Shailesh's question.

Moreover, it doesn't matter if DB2 client displays the date in that format. You can always change the format for your application. Actually, that is a default date format of DB2.