aspose file tools*
The moose likes JDBC and the fly likes SQL Statement not Returning Date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Statement not Returning Date" Watch "SQL Statement not Returning Date" New topic
Author

SQL Statement not Returning Date

Steve Dyke
Ranch Hand

Joined: Nov 16, 2004
Posts: 1478
    
    1
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

Joined: May 26, 2003
Posts: 30957
    
158

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96

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

Joined: Nov 16, 2004
Posts: 1478
    
    1
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

Joined: Nov 16, 2004
Posts: 1478
    
    1
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Statement not Returning Date