• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

comparing dates in SELECT statment

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 216
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 216
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).]
 
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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'))
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic