File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Ordering the retrieved dates from Database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Ordering the retrieved dates from Database" Watch "Ordering the retrieved dates from Database" New topic
Author

Ordering the retrieved dates from Database

Piyush Mattoo
Ranch Hand

Joined: Mar 12, 2007
Posts: 30
I am retrieving the data from Oracle using JDBC API. I want to get the last 24 months starting from current month and the respective count of records.
I wrote the query as shown below:
(TIMESTAMP is of Data type DATE)

However, i cannot order by MONTH_YEAR as that will be ordered by alphabets(A,B,C..) as MONTH_YEAR is a string.
What i would appreciate is if anyone could point me how to order the result by date either on the DB query or on the Java end.

Thanks in advance!
Andrew Monkhouse
author and jackaroo
Marshal Commander

Joined: Mar 28, 2003
Posts: 11503
    
  95

I tried putting your SQL inside code blocks, but it still was hard for me to read. This is possibly just a question of style (and I have yet to find a good style for SQL that everyone agrees to). I think your code can be rewritten as:

My first question is: why do you need the sub-clause?

Within your sub-clause, you have access to the timestamp, which I assume is a timestamp. If so, you could order it according to the timestamp itself.


The Sun Certified Java Developer Exam with J2SE 5: paper version from Amazon, PDF from Apress, Online reference: Books 24x7 Personal blog
Andrew Monkhouse
author and jackaroo
Marshal Commander

Joined: Mar 28, 2003
Posts: 11503
    
  95

I don't have access to an Oracle database right now, but I would have thought that this could be simplified:

The to_char function converts the date field according to your format specification. So specifying that the conversion should use a dash, then later converting the dash into an underscore seems redundant - couldn't you just specify the underscore in the to_char format definition?
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Hi,

use EXCTRACT conversion function, it extracts months and years as numbers,
it is available starting from Oracle 9i:

Also avoid using TIMESTAMP as a column name, TIMESTAMP is a data type,
it could cause confusion and Oracle can give you strange syntax errors in some cases.
But if you must, enclose it in brackets - like this- "TIMESTAMP".
Piyush Mattoo
Ranch Hand

Joined: Mar 12, 2007
Posts: 30
@Andrew: I would imagine i cannot order by timestamp though i can by MONTH_YEAR which would give me the
alphabet ordering.

You are right, No need of replace function

@Ireneusz: Point noted. This does work. Only thing is that for past 24 months, we need to subtract 23 months from sysdate.
WHERE "TIMESTAMP" >= TRUNC((ADD_MONTHS(SYSDATE, -23)), 'MM')

Thankyou for your valuable opinions.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Ordering the retrieved dates from Database