aspose file tools*
The moose likes JDBC and the fly likes db2 year 2000 invalid date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "db2 year 2000 invalid date " Watch "db2 year 2000 invalid date " New topic
Author

db2 year 2000 invalid date

arunij katiliyar
Greenhorn

Joined: Mar 09, 2005
Posts: 26
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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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

Joined: Mar 09, 2005
Posts: 26
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
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: db2 year 2000 invalid date