1

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Dileepan
  • 123
  • 1
  • 4
  • 8
  • 2
    Please give context -- where is this value, is it part of another statement, does it come from an Excel spreadsheet or something translated from Urdu or what? Assume we don't know your situation. – arcy Apr 23 '14 at 10:52
  • I'm using java with hibernate.For example i need to update updated_Timestamp(TIMESTAMP) filed in Employee table. I manually gave updated_Timestamp field value as 0001-01-01 00:00:00.000000 in my java code and tried to update(dao.update(Employee)) the table. while updating I'm getting -181 error. – Dileepan Apr 23 '14 at 11:07
  • Which DB2 jcc driver version are you using? – Simon Verhoeven Apr 30 '14 at 11:20
  • Was the error message that you posted here originally in all-caps, or did you make it that way just for this question? –  Jul 01 '14 at 08:00

3 Answers3

2

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

AngocA
  • 7,655
  • 6
  • 39
  • 55
  • Write out the date you are inserting in the database (system.out) in order to verify that it is providing the right format. – AngocA Apr 23 '14 at 12:41
  • System.out is printing 0001-01-01-00.00.00.0 timestamp in my console. If i try to udpate via DB by writing a query, it is working fine. But vis java(with hibernate) it is throwing -181. – Dileepan Apr 23 '14 at 13:03
  • Ok, now we know that this is a Hibernate configuration problem, not DB2. Try to change the logging level in hibernate in order to write the SQL sentences it is sending to the database: http://stackoverflow.com/questions/311408/turning-off-hibernate-logging-console-output – AngocA Apr 23 '14 at 13:35
  • You can eventually force the Date format: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0007107.html – AngocA Apr 23 '14 at 13:38
  • Actually is not Hibernate, is the data format in Java. Please check: http://docs.oracle.com/javase/7/docs/api/java/text/DateFormat.html – AngocA Apr 23 '14 at 13:42
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

Ria
  • 10,237
  • 3
  • 33
  • 60
Dileepan
  • 123
  • 1
  • 4
  • 8
1

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.

user2338816
  • 2,163
  • 11
  • 11