Sergio, as you map your dates to strings the database sort them like strings. I suggested to change the pattern to generate string that are sorted as you would expect from your dates.
I know that customer is always right but sometimes he don't know what he causes with his requiremnets. I would give it a try to ask if he can change that pattern. If that's not possible it gets a bit difficult. I can see only to possible solutions: 1) Load all the objects and compare dates by your own code. 2) Use castor SQL passthrough query that look similar to: CALL SQL select <fields> from <table> where (SUBSTR(mydate,6,4) < year) or ((SUBSTR(mydate,6,4) = year) and ((SUBSTR(mydate,3,2) < month) or ((SUBSTR(mydate,3,2) = month) and (SUBSTR(mydate,0,2) < day)))) AS MyClass The <fields> need to be in the same order as if castor translates a OQL query to SQL. You can view the SQL queries generated by castor if you switch on debiging. You need to replace the SUBSTR function calls with an appropriate function from the SQL syntax of your database. Ralf Sergio Collantes schrieb: > Hello! > > First of all, I'm sorry if I can't follow the thread, > but sometimes it's impossible for me to do it. > > In order to map the dates with a different pattern I > shuld need to change the database strings, wich is > something that i can't do (specifications from the client, > i know is a crazy sort, but "the client is always right" :) > Is there any other way to do it? I expected that once Castor > has made the query to the database, the framework > ordered the objects, not the results as they come from the > database. > > > > > > Hi Sergio, > > my first thought was that you have problems how to include dates in your > query but as you stated now it seams you managed that. Having a second > look on your code I found that you will have the problem that your query > returns the wrong records. This relates to the way you map the dates to > your database with string and the pattern 'dd/MM/yyyy'. With the pattern > you use a date '01/12/2004' comes before '13/01/2004' which is obviously > not what you want. I suggest you to change your mapping to 'yyyy/MM/dd'. > This is also easy to read but has the advantage that you'll be able to > sort your records with that field and your query will return the results > you expect. > > Ralf > > > Sergio Collantes schrieb: > >> Hello, Ralf. >> >> Thanks for your quick answer, but it does not solve >> the problem. I have to say that is my fault, I explined >> the problem in a wrong way. What I actually do is what >> you suggested, bind the java.util.Date to the query (when >> I posted that simple example, I forgot to do so, but in my >> real app it is just what I do). Anyway, is does not work either. >> >> Thank again, Ralf. >> >> >> >> On Mon, 22 Aug 2005 13:53:15 +0200, Ralf Joachim >> <[EMAIL PROTECTED]> wrote: >> >>> Hi Sergio, >>> >>> you should be able to execute such a query with: >>> >>> OQLQuery query = db.getOQLQuery( >>> "select a from MyClass a where a.mydate > $1"); >>> query.bind(expirationDate); >>> QueryResults results = query.execute(); >>> >>> Regards >>> Ralf >>> >>> >>> Sergio Collantes schrieb: >>> >>>> >>>> Hi all: >>>> >>>> I've been having trouble when trying to >>>> build an OQL query using java.util.Date types. >>>> Suppose we have a simple class "MyClass" with >>>> a java.util.Date filed named "myDate". >>>> >>>> The query goes like this: >>>> >>>> select a from MyClass a where a.mydate > expirationDate >>>> >>>> Let's suppose that the object expirationDate is declared >>>> in this way: >>>> >>>> Calendar calendar = Calendar.getInstance(); >>>> calendar.set (...) // Whe set the expiration date >>>> java.util.Date expirationDate = calendar.getTime(); >>>> >>>> The field myDate is a varchar in the database, and the >>>> mapping is correct (i.e. the data is converted to Date >>>> correctly in the JDO, i have tried it and it works). >>>> The mapping is declared as follows: >>>> >>>> <field name="mydate" type="date"> >>>> <sql name="mydate" type="char[dd/MM/yyyy]"/> >>>> </field> >>>> >>>> I assume in doing the wrong query, ¿can anybody tell >>>> me what do I have to do? >>>> >>>> ------------------------------------------------- >>>> If you wish to unsubscribe from this list, please send an empty message >>>> to the following address: >>>> >>>> [EMAIL PROTECTED] >>>> ------------------------------------------------- >>> >>> >>> >>> ------------------------------------------------- >>> If you wish to unsubscribe from this list, please >>> send an empty message to the following address: >>> >>> [EMAIL PROTECTED] >>> ------------------------------------------------- >>> >> >> >> >> ------------------------------------------------- >> If you wish to unsubscribe from this list, please send an empty message >> to the following address: >> >> [EMAIL PROTECTED] >> ------------------------------------------------- > > ------------------------------------------------- If you wish to unsubscribe from this list, please send an empty message to the following address: [EMAIL PROTECTED] -------------------------------------------------
