• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

Problem with date

 
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.
 
author & internet detective
Posts: 39343
755
Eclipse IDE VI Editor Java
  • 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
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!