• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem with date

 
manisha ankolekar
Greenhorn
Posts: 10
  • 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: 34071
331
Eclipse IDE Java VI Editor
  • 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
  • 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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic