• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem with date

 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
That feels good. Thanks. Here's a tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic