This week's book giveaway is in the Performance forum.
We're giving away four copies of The Java Performance Companion and have Charlie Hunt, Monica Beckwith, Poonam Parhar, & Bengt Rutisson on-line!
See this thread for details.
Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Statement not Returning Date

 
Steve Dyke
Ranch Hand
Posts: 1629
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using the following to retreive a remote record. One of the dates was entered wrong from another app. It is 04/05/0009. My code says the field is null. How can I get it to return this invalid date so I can edit it to the correct date?

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34686
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does the database think the date is null? It sounds like it does. If this is the case, you wouldn't be able to get it back.
 
John Kimball
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

If the database isn't treating it as a null value and this is a real problem try this:
- Add another column in your SQL statement which converts the problem date column to a char.
- Manually parse the string column

So for example:


I'm not sure what DBMS you're using, so I can't do any better than that.
In some DBMS, the conversion is as simple as appending a zero-length string to the column.

Also, this could very much be a problem in the JDBC driver, since Date and DateFormat starts from 1/1/1970.
So the NULL could be stemming from something along the lines of :
 
Steve Dyke
Ranch Hand
Posts: 1629
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John Kimball wrote:

I'm not sure what DBMS you're using, so I can't do any better than that.
In some DBMS, the conversion is as simple as appending a zero-length string to the column.

Also, this could very much be a problem in the JDBC driver, since Date and DateFormat starts from 1/1/1970.
So the NULL could be stemming from something along the lines of :


I am using AS400(iSeries) which is a db2 database. I have tried to modify my SQL statement as:



If I modify the date to less than 01/01/1940 it returns a null. I don't understand why it is not treating teh date as a string.
 
Steve Dyke
Ranch Hand
Posts: 1629
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey, I got it to work. I added a format to the CHAR() function(example: CHAR(myDate,USA) and all is well.

Thanks for all the help.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic