This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Problem with querying Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with querying" Watch "Problem with querying" New topic
Author

Problem with querying

Peter Phung
Ranch Hand

Joined: Dec 06, 2001
Posts: 138
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.


Pete<br />"Reality is an illusion <br />brought on by a lack of <br />drink, drugs and smut"
Bosun Bello
Ranch Hand

Joined: Nov 06, 2000
Posts: 1510
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.


Bosun (SCJP, SCWCD)
So much trouble in the world -- Bob Marley
Peter Phung
Ranch Hand

Joined: Dec 06, 2001
Posts: 138
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

Joined: Jul 09, 2001
Posts: 1879

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

Joined: Dec 06, 2001
Posts: 138
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

Joined: Jul 09, 2001
Posts: 1879

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

Joined: Dec 06, 2001
Posts: 138
I guess i'll have to change the table structure to take account of the date format.
Thanks for the help
Pete
 
 
subject: Problem with querying
 
Similar Threads
eligiblity for taking SCWCD,SCBCD..... etc ???
writing to an Action form
keyboard usage of a JOptionPane
diff between JSF and My Faces
Exception Handling