wood burning stoves 2.0*
The moose likes Servlets and the fly likes comparing dates in SELECT statment Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "comparing dates in SELECT statment" Watch "comparing dates in SELECT statment" New topic

comparing dates in SELECT statment

Shumaiza Manzoor

Joined: Sep 03, 2001
Posts: 6
hi all,i am shumaiza,
i am using servlets,and database access,
i have one date column named ticket_time in a table.
now i want to use this date column in my select statment's where clause in my class, but it gives error:
"java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression "
In table values of ticket_time column are in this format :
my select statment and some code in my class are below:
String str_t_time = req.getParameter("param");
rs = stmt.executeQuery("select * from Tick WHERE ticket_time = " + "'" + str_t_time +"'" );
my class is working fine if i remove ticket_time and use any other column in where clause.
but i need to search by date.
plzz help.
tahnx in advance.
ernest fakudze
Ranch Hand

Joined: Aug 27, 2001
Posts: 216
I do not use the date field in MS Access. This is because many dbms's represent dates in defferent formats. I just use a text field instead. Here is howto...
Suppose I wanted the format yyyymmdd, I would then create a text field and believe me SQL knows that the string 19990808 is less than 20010808 and you can also use the Java substring() method to format the date like: 1999-08-08 or whatever.
Now you can say sth. like
select * form members where dob <=" + "'" + formParam + "'";
form param would be some input from a form field in this case.
I believe that this is the best way to do dates in dbs.
Good luck

In a time of drastic change it is the learners who inherit the future. The learned usually find themselves equipped to live in a world that no longer exists.<br />Eric Hoffer
aamir abbas

Joined: May 04, 2001
Posts: 19
Hi Shumaiza,
i think, u have to check ur date format both in database and in variable ( getting from request.getParameter ). They might not be of same format.
Shumaiza Manzoor

Joined: Sep 03, 2001
Posts: 6
ernest u r right,
but i want System Date to be inserted in the data base by default, user have no interaction with this field, thats why i set default date on the column by date() method of access so if i set the text field to text how can i get current date automically.

[This message has been edited by Shumaiza Manzoor (edited September 04, 2001).]
ernest fakudze
Ranch Hand

Joined: Aug 27, 2001
Posts: 216
First you get the current date using java.util package like
Date todaysDate = new Date();
You then format it to your liking using the DateFormat class.
You then convert the formated date to a String using he substring() method. At this point do not add anything to it so if your dateformat was yyyymmdd, then you will store a string like 20010904 in the db.
You then store it in db with every entry as a string
To get it back formated you will have to use the substring() method e.g
while rs.next() {
// declare a var for the split date here
String splitDate = "";
String date = rs.getString("date_stamp")
//Format the date using substring
// if we had the format yyyymmdd..
splitDate = date.substring(0,4) + "-" + date.substring(4,6) +
"-" + date.substring(6,8);
- Output it back to browser using HTMl and relax..lol!
You will now get a nice looking date like.
and you can even re-arrange the format using the substring() method.
In order to format your dates you will need to study the DateFormat packeges..
Good luck!!

[This message has been edited by ernest fakudze (edited September 04, 2001).]
[This message has been edited by ernest fakudze (edited September 04, 2001).]
Jack Wiesenthaler
Ranch Hand

Joined: Jul 26, 2001
Posts: 75
The query has an error because you are not comparing like types, you will need to do a typecast like this... I have forgotten my TSQL but the PL/SQL equivalent will be something like...
select * from Tick where trunc(ticket_time) = trunc(to_date('9/3/2001','MM/DD/YYYY'))
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
subject: comparing dates in SELECT statment