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 querying

 
Peter Phung
Ranch Hand
Posts: 138
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I know this question isn't particularly related to Java but...
I'm trying query my database between september 2001 and january 2002, but my between statement does not work because september is after january. does anyone know how to get round this problem?
Any help would be much appreciated.
 
Bosun Bello
Ranch Hand
Posts: 1510
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You have to post some code. As long as you include the year in your query, and it's in the format the date is stored in the DB, I see no reason why it shoyuld not return what you want.
 
Peter Phung
Ranch Hand
Posts: 138
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My sql query is
"SELECT.........FROM........WHERE Month.ID BETWEEN 9 AND 2 AND Year BETWEEN 2001 AND 2002"
I've run this query a few times and it returns nothing. the column names and table names are in the right format.
Again i would appreciate any suggestions
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are using oracle, there is something that may be wrong with your SQL statement. In my Oracle docs it says "Values specified with the BETWEEN operator are inclusive. You must specify the lower limit first". You do not follow this rule in your query.
Jamie
 
Peter Phung
Ranch Hand
Posts: 138
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
the period that I am actually trying to query is between september 2001 and febuary 2002. My lower limit is correct but the database does not recognise september as appearing before febuary.
Pete
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My last post won't work at all!
Sounds like you may have to rethink the way you store your date data. It can be awkward to compare the dates if you separate the year, months, and dates into number columns. You'll have to do some fancy innefficient conversions every time you need to query the database with a between dates in the where clause. If you store the dates separately, store them as VARCHAR2 (4) for year and VARCHAR2(2) for month. This will work:
" year||month BETWEEN '200109' AND '200202' ". The dates always have to be yyyymm or yyyymmdd with padded 0's for months/days < 10. This is how my shop deals with dates as well, and it seems to be working pretty good.
If you can't change the table structure, I don't know of any way of comparing except using date/character conversion functions:
...WHERE TO_CHAR(year)||LPAD(TO_CHAR(month), 2 , '0') BETWEEN '200109' AND '200202'
The above where clause will work for you if your year and month columns are numbers.

Jamie
 
Peter Phung
Ranch Hand
Posts: 138
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess i'll have to change the table structure to take account of the date format.
Thanks for the help
Pete
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic