• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Serching record using date/time field of MSAccess

 
Roshan Wankhade
Greenhorn
Posts: 21
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
I'm making project for library management in which user searching by 'bookNumber' went fine which is number in Ms Access db.
But when i search through 'dateBorrowed' which is in date/time format in database, i haven't get anything form database....
the field i'm checking is in String format which is getting through textfield in yyyy/mm/dd format.
my db table also contain yyyy/mm/dd format date record... but it shows date/time format in design view of table...
my query is....

SELECT * FROM qryBarrowedBooks WHERE " + cbSearchIn.getSelectedItem().toString().replaceAll(" ", "") + " LIKE '%" + txtSearchFor.getText() + "%' ORDER BY BookNo ASC

where txtSerchFor is my textField, cbSearchIn is my checkBox where i select type of field i'm serching for...

which went fine for other records search, but haven't return anything in case of date...
Please, help me...
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch Roshan.

This line confuses me

db table also contain yyyy/mm/dd format date record



it can only be in a format if it is a string/VARCHAR etc, date fields are usually stored as a number of milliseconds since some date decided upon by the database manufacturer.

so if you have a date field you need to convert your string into a java date, you do this using a SimpleDateFormat.
I can't tell you how to use it as I have to search for an example every time, so i know there are many good examples out there.
 
William P O'Sullivan
Ranch Hand
Posts: 859
Chrome IBM DB2 Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wendy is right.

Your best bet in this case since you know your checkbox value is "yyyy/mm/dd"
is to use a SQL function such as to_char() or to_date() depending on the db in use.

This will match up both sides of the LIKE.

you should test first by selecting FUNCTION(dateField,format) from table to get it right.
WP
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MS Access databases allow a format to be specified for table columns (fields). However, this format is used when displaying the value in MS Access GUI (in a table view, for example); when you manipulate the data via SQL, you still have to handle the column according to it's data type (probably a Date in your case). So Wendy and William are right. See also Avoid implicit conversions.
 
Roshan Wankhade
Greenhorn
Posts: 21
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi...
Thanks for reply...
Problem is solved!!!

I just change the field in database from date/time type to Text type...

Now its work for searching by date...
 
William P O'Sullivan
Ranch Hand
Posts: 859
Chrome IBM DB2 Java
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's not quite the correct solution.

For instance, how would you ever be able to check for books loaned by month, or most popular on a Friday?

Good database design and correct datatypes are critical to any application.

A number should be a number, a date a date etc..

This design allows much more flexibility in the future.

Oh well, enough ranting.. Well done, I guess.


WP
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic