This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem with date

 
manisha ankolekar
Greenhorn
Posts: 10
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33697
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
manisha ankolekar
Greenhorn
Posts: 10
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply
Can You please tell me how should I convert the Text into date

Please help
Thanks
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic