Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

db2 year 2000 invalid date

 
arunij katiliyar
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
and if I remember correctly, DB2 by default can automatically convert MM-DD-YYYY and YYYY-MM-DD.
 
arunij katiliyar
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic