SQLCODE -181 THE STRING REPRESENTATION OF A DATETI

Question

I'm getting " THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE" when I give 0001-01-01 00:00:00.000000 for timestamp field from input. But when I change 0001 to 0002 given as 0002-01-01 00:00:00.000000 for timestamp field from input, my code is working fine without any error. Why it is so? Will java not support 0001-01-01 00:00:00.000000 value for timestamp?

I'm using DB2 as my Database.

Answer 1

In DB2, SQLCODE SQL0181 means that the given representation is not valid

db2 ? sql0181

However, when I write in DB2 your date, it accepts it:

db2 "values timestamp('0001-01-01 00:00:00.000000')"

1
--------------------------
0001-01-01 00:00:00.000000

  1 record(s) selected.

However, when I put an invalid date, like 13 months, it returns your error code

db2 "values timestamp('0001-13-01 00:00:00.000000')"

1
--------------------------
SQL0181N  The string representation of a datetime value is out of range.

I think you have a problem of locale and regional settings that does not correspond to the provided datetime.

Run this command, and then start modifying the date

db2 "values current timestamp"

Table 6. Datetime Limits

• Smallest TIMESTAMP value 0001-01-01-00.00.00.000000000000
• Largest TIMESTAMP value 9999-12-31-24.00.00.000000000000

http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0001029.html

Answer 2

The problem is with the JDK version which i'm using,. In JDK 1.7 the default timestamp 0001-01-01 00.00.00.000000 is not supported, I changed my JDK from 1.7 to 1.6, and ran my program, the issue got resolved.

If want to use JDK 1.7 then the default timestamp should be 0002-01-01 00:00:00.000000. In case of JDK 1.6 the default timesatmp is 0001-01-01 00:00:00.000000

Answer 3

There is a documented behavior related to a TIMESTAMP value of 0001-01-01 00:00:00.000000 (as well as for 9999-12-31-24.00.00.000000000000) in JDBC/SQLJ for DB2. Not enough is known about the query itself, the code that submits the query, the column attributes, the DB2 version nor the platforms to tell how it might apply in this case.

Review the Use of PreparedStatement.setTimestamp to set values in TIMESTAMP WITH TIME ZONE columns topic in the DB2 for z/OS Information Center, and pay attention to the descriptions relating to the particular timestamp values. The topic is about how the driver can handle those values, though it doesn't really give much internal detail. I suspect that the driver is making a slight change to the query because it doesn't know the exact data type at the server.

There are related data types that are possible. The driver might generate a slightly inappropriate SQLDA based on an assumption that gives the unexpected result.

It's likely that much more specific searches could find more info, but I don't know that an actual "answer" will be found without knowing a lot more about the driver internals. There will likely be very similar conditions found for any of the JDBC/SQLJ drivers depending on versions, etc. If this case isn't exactly about PreparedStatement.setTimestamp, it probably doesn't matter. There's bound to be related classes/methods using common code.