jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes Problem with date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with date" Watch "Problem with date" New topic
Author

Problem with date

manisha ankolekar
Greenhorn

Joined: Nov 26, 2006
Posts: 10
I have witten SQL query as:
"Select * from tbl where date between 'Jan 2 2008' and 'Feb 28 2008'";

Records in table are:

Name Date
aaa Jan 5 2008
bbb Jan 26 2008
ccc Jan 26 2008
ddd Feb 27 2008


Now when I run this query I get only last record i.e

ddd Feb 27 2008

When I run query as

"Select * from tbl where date between 'Jan 1 2008' and 'Jan 31 2008'";
I get all records inculding Feb ones ie

Name Date
aaa Jan 5 2008
bbb Jan 26 2008
ccc Jan 26 2008
ddd Feb 27 2008

I'm using MS Access db and I have kept date field with Text data type

I'm not getting why this problem is occuring.
Please help
thank you.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30123
    
150

Manisha,
I'm surprised you get anything back.

A date should be stored in a date column, or at least converted to a date type in the where clause. You are doing a text search on data that isn't fully text. This means the database is trying to find strings between "J.." and "F.."

This is inherently reliable. "January 5" is before "January 28" in an ASCII sort. Which is not what you want.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
manisha ankolekar
Greenhorn

Joined: Nov 26, 2006
Posts: 10
Thanks for the reply
Can You please tell me how should I convert the Text into date

Please help
Thanks
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Problem with date
 
Similar Threads
problem related to timestamp
Priority Queue Ordering Question
how to solve this
mysql & incomplete insertions!
Returning just a STRING from.......