File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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 {
// 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!
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'))
I agree. Here's the link:
subject: comparing dates in SELECT statment
It's not a secret anymore!