[SQL0181] Value in date, time, or timestamp string not valid.

Posted on 2004-09-20
Medium Priority
Last Modified: 2008-01-09
I am getting the following exception when looping through my resultset when I encounter the following error:
Billing.buildItemTable(): SQLException2 = [SQL0181] Value in date, time, or timestamp string not valid.

The while loop runs successfully over 240+ times before the exception is thrown.  The last logging statement I see is "row added!" so it seems that the code is throwing an exception when trying list.next().    I first thought maybe the date value I was getting from the database was invalid, but I'm containing that exception in its own try / catch block.  I've looked in the database (DB2) and cannot see anything wrong with the next row's data.  

Here's  the code:

            try {
                  while(counter < maxCount && list.next()) {

                        // Format the date.
                        try {
                              System.out.println("getting date...");
                              Date date = list.getDate("TRANSACTION_DATE");
                              System.out.println("date = " + date);
                              sDate = DATEFORMAT.format(date );
                        catch(SQLException sqle) {
                              System.out.println("Billing.buildItemTable(): SQLException1 = " + sqle.getMessage());
                        String transType = list.getString("TYPE").trim();
                        System.out.println("transType = " + transType);

                        int sequence = list.getInt("SEQUENCE_NUMBER");
                        System.out.println("sequence = " + sequence);

                        String transNumber = list.getString("NUMBER");
                        System.out.println("transNumber = " + transNumber);
                        String po = list.getString("PO");
                        System.out.println("po = " + po);

                        String description = list.getString("DESCRIPTION");
                        System.out.println("description = " + description);

                        // Add the item.
                        System.out.println("adding row...");
                        items.addRow(sDate, transNumber, po, description);
                        System.out.println("row added!");
            catch(SQLException sqle) {
                  System.out.println("Billing.buildItemTable(): SQLException2 = " + sqle.getMessage());
Question by:nateforrest1
  • 3
  • 2
  • 2
LVL 18

Expert Comment

ID: 12105146
If your code is retrieving more rows than exists, subtract 1 from maxCount.

Am I way off base?  

LVL 62

Accepted Solution

Julian Hansen earned 1500 total points
ID: 12105562
The obvious explanation is that it is something wrong with the data. If you did 240 successful loops then by induction you can say that your code is correct. The code is static and with exception of the counter increment and the list.next nothing changes. I doubt it is the counter statement - 240 items is not about to cause an overflow and one would think that list.next() has been written in such a way that it can handle end of list conditions et al gracefully.

That means the only thing left is data - I would do a bit of data analysis on the input query to see what potential funnies there are there.


Author Comment

ID: 12105694
Actually there are 431 rows in the ResultSet, but it keeps throwing the Exception on the same row.  I have about 10 large customers that were giving me the same exception so I did some testing and found that the Exception was thrown each time the ResultSet reached the 287th row.  I don't think there is anything magical about this number, i.e. I limited the columns I was bringing back in my ResultSet and I was able to get all the rows.  

I started thinking maybe this is an out of memory exception, so I increased the amount of memory: java -Xms256M -Xmx256m com.test.Billing and added a Runtime object to monitor the memory being used.  

      Runtime rt = Runtime.getRuntime();
      System.out.println("Free/total memory:");
      System.out.println(rt.freeMemory()+"   "+rt.totalMemory());      

However, the Runtime object showed plenty of free memory available.  
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

LVL 18

Expert Comment

ID: 12105768
I agree with julian...sounds like a problem with the data.  I've seen where a hard return (ctrl+enter) in a field can cause problems.  Can you check the data using another method?


Author Comment

ID: 12105800
I first suspected bad data so I changed the query to start in the middle of the first ResultSet and the program was able to return rows past the record in question.  
LVL 62

Expert Comment

by:Julian Hansen
ID: 12105885
Good thinking batman.

Well that rules out a particular record as being the problem.

What happens (just as a matter of interest) if you don't try and catch the exceptions i.e. comment out your try / catch? Not suggesting this as a solution but it would be interesting to see what happens.


Author Comment

ID: 12106467
I'm not sure if its something quirky with AS400s, but I spoke to a programmer who had worked here for several years and he said he had seen something like this in the past.  He instructed me to add the following to my host name when setting up JDBC:

;date format=iso

This is an excerpt from IBM's web site:
Applications that require ISO formats or JDBC-defined string conversions must set the toolbox connection properties that control the date and time format to ISO.

This fixes the problem.  So it appears maybe it is some sort of data problem.  I'm still a bit wary of this since I had tested the data for bad data.  Oh well.  Thanks to everyone for all the help!  julianH I'm recommending partial credit to you!

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question