Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

SQL Statement not Returning Date

 
Ranch Hand
Posts: 2003
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?

 
author & internet detective
Posts: 39986
806
Eclipse IDE VI Editor Java
  • 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.
 
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: 2003
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: 2003
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.
 
You firghten me terribly. I would like to go home now. Here, take this tiny ad:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic