comparing dates in SELECT statment

[Shumaiza Manzoor]

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 :
01-Sep-01
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]

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

[aamir abbas]

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.
Aamir

[Shumaiza Manzoor]

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.
thanx

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

[ernest fakudze]

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.
2001-08-08
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]

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'))