SQL Statement not Returning Date

[Steve Dyke]

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?

try{
     	
			Statement stmt = arg0.createStatement();
			String sql = "select esthrs, prcntcom, assnto, dwgrelt, dwgrele, dwgreldt, dlcmnts from webprddt6.drawmext3 Where recseq = " + arg1;
			System.out.println(sql); 	       
			ResultSet rs = stmt.executeQuery(sql);
			if(rs.next()){
				
				System.out.println(rs.getString(4));
				
				this.estHrs = rs.getString("esthrs");
				this.remHrs = rs.getString("prcntcom");
				this.assnTo = rs.getString("assnto");
				this.dwgEDR = rs.getString("dwgrelt");
				this.dwgECD = rs.getString("dwgrele");
				this.dwgRECD = rs.getString("dwgreldt");
				this.dlComments = rs.getString("dlcmnts");
       		
       		
			}	

[Jeanne Boyarsky]

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]

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:

                String sql = "select datecol, SOMECONVERTFUNCTION(datecol) as datecol_char from webprddt6.drawmext3 Where recseq = " + arg1;
...
                this.datecol = rs.getString("datecol");   
                this.datecol_char = rs.getString("datecol_char");
                if (this.datecol==null) {
                     // Parse datecol_char and/or correct
                }

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 :

     DateFormat df = new DateFormat();
     Date date = df.parse(myString);

[Steve Dyke]

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 :

     DateFormat df = new DateFormat();
     Date date = df.parse(myString);

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

String sql = "select esthrs, prcntcom, assnto, CHAR(dwgrelt) As dwgrelt, dwgrele, dwgreldt, dlcmnts from webprddt6.drawmext3 Where recseq = " + arg1;

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]

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.