Home arrow static arrow Java Programming [Archive] - Date format /structure for JDBC
Warning: Creating default object from empty value in /www/htdocs/w008deb8/wiki/components/com_staticxt/staticxt.php on line 51
Java Programming [Archive] - Date format /structure for JDBC
This topic has 8 replies on 1 page.

Posts:315
Registered: 02-07-03
Date format /structure for JDBC  
Aug 1, 2003 12:26 PM



 
Hi, can anyone tell be what format a date should be in when querying a database. I have some old code that was transfered to a new app server which follows Java standards very strictly. Now it gives me problems when comparing dates, for example a query that finds records for a date BETWEEN two other dates. The old format was dd-mon-yyyy, but this doesn't work anymore and neither does dd-mon-yy OR yyyy-mon-dd. When I do the query in the text editor(SQL Plus) it works, but not within the Java code.

Any ideas?
Thanks
 

Posts:19,725
Registered: 9/26/01
Re: Date format /structure for JDBC  
Aug 1, 2003 12:31 PM (reply 1 of 8)



 
You don't put it in a format. You use PreparedStatement instead, something like:
PreparedStatement stmt = connection.createStatement("SELECT * from table where dateField = ?");
stmt.setDate(1, someDateVar);
ResultSet rs = stmt.execute();
 

Posts:315
Registered: 02-07-03
Re: Date format /structure for JDBC  
Aug 1, 2003 12:46 PM (reply 2 of 8)



 
yes but in your example what format is "someDateVar". It needs to follow a structure so that the database is abale to compare it.
 

Posts:19,725
Registered: 9/26/01
Re: Date format /structure for JDBC  
Aug 1, 2003 12:53 PM (reply 3 of 8)



 
No, someDateVar is a variable of type Date or Timestamp, not a String.
 

Posts:21,719
Registered: 98-02-20
Re: Date format /structure for JDBC  
Aug 1, 2003 12:53 PM (reply 4 of 8)



 
WarnerJA is correct.

That's what the JDBC driver does for you. It takes care of all that.
 

Posts:315
Registered: 02-07-03
Re: Date format /structure for JDBC  
Aug 1, 2003 1:06 PM (reply 5 of 8)



 
Yes, but if am getting a date from an html page and constructing it the date from a String, don't i have to contruct it into a format of some sort("dd-mon-yy").

Thanks
 

Posts:21,719
Registered: 98-02-20
Re: Date format /structure for JDBC  
Aug 1, 2003 1:17 PM (reply 6 of 8)



 
I see what you mean now. Yes, format matters then. Your HTML page will assume that the string that's typed into the text box will be a valid date string using a format that you choose.

You should turn that String into a java.util.Date using java.text.DateFormat.parse(). Be sure to call setLenient(false) on the DateFormat instance before you call parse(). That way you'll get a java.text.ParseException if the String isn't valid according to your pattern and you can send an error page back to the client. (OR validate the pattern on the client side using JavaScript before the page is submitted.)

Once you've got a valid java.util.Date for your DateFormat instance, create a java.sql.Date to pass to the ava.sql.PreparedStatement.setDate method.

Warner's point is that by the time you get around to passing the java.sql.Date to PreparedStatement, all that validation stuff is supposed to be done.
 

Posts:315
Registered: 02-07-03
Re: Date format /structure for JDBC  
Aug 1, 2003 1:21 PM (reply 7 of 8)



 
great, thanks very much
 

Posts:957
Registered: 3/31/04
Re: Date format /structure for JDBC  
Jul 21, 2004 6:39 AM (reply 8 of 8)



 
progeny keywords:
Date Format SQL

(great post/answered my q)

Q
 
This topic has 8 replies on 1 page.