Easy way to select between two dates?

halcyon · **Joined:** Wed Dec 17, 2003 1:58 pm **Posts:** 102

Hi I am trying to select my object within a date range. The problem I have is I need to insert it into the actual select statement as opposed to doing something like between :start and :end. The field in the db is a datetime field.

This is the query I have been attempting but I get empty sets all the time, it seems like its comparing the string values as opposed to the date values..

Code:

from cmcflex.salesweb.model.marketing.Prospect as prospect where ( ( prospect.nextActivityDate < '04/01/2004 00:00:00' ) )

and I have 2 entries in the db dated before that time.

drj · **Posted:** Fri Jan 23, 2004 1:28 am

What database are you using?
If you wish to embed your string, you may need to call database specific functions (e.g. TO_DATE) to convert your string to a date.
This of course comes with the risk of writing database independent code.

for Oracle:

Code:

select * from Prospect as prospect where prospect.nextActivityDate >= TO_DATE([String], [Format])

halcyon · **Joined:** Wed Dec 17, 2003 1:58 pm **Posts:** 102

My db is Mysql 4.x... I don't mind about the db dependent code.. we'll always be using mysql as best I know anyway.
-David

halcyon · **Joined:** Wed Dec 17, 2003 1:58 pm **Posts:** 102

For anyone else that searches this I figured out how to do it in mysql.. its

select blah blah where nextActivityDate <= '2000-02-02'

the date must be in the format yyyy-mm-dd or yyyymmdd and it works great.

Good luck and thanks!

bertrand · **Posted:** Sun Jan 25, 2004 7:09 pm

We are always using the Query API and never had any problem when querying for dates... We always thought that Hibernate will take care of DB-specific convertion for us.

Why not writing your query as follows:

Code:

Query q = s.createQuery("from cmcflex.salesweb.model.marketing.Prospect as prospect where prospect.nextActivityDate < :maxDate" );
q.setParameter( "maxDate", <your date object> );

return q.list();

Should work without any problem, no ?

bertrand · **Posted:** Sun Jan 25, 2004 7:11 pm

AFAIK, Hibernate is using a PreparedStatement with parameters.
It is then your JDBC driver that will do the conversion of your date object to the appropriate format.

Just make sure you set the hours, min and seconds to 0 if you want to query for DATE (without time) ;-)

halcyon · **Joined:** Wed Dec 17, 2003 1:58 pm **Posts:** 102

Normally I would just use a prepared statement type thing with hibernate however this query is built from text and i'm trying to avoid having to do any parameter setting, since everything is essentially dynamic.

michael · **Posted:** Mon Jan 26, 2004 4:40 pm

You should in every case do parameter setting! Building the whole query manually opens the door for all kinds of nasty security issuse and database dependencies. For dynamic query building, also take a look at the query by criteria api.

mkthomas5 · **Joined:** Fri Jan 23, 2004 12:16 pm **Posts:** 5

Also if you try and do everyting dynamic, you never really get to take advantage of the database caching statements.
In Oracle **ALWAYS USE BIND VARIABLES***

I know you are not in Oracle, but others are.
and its a good pratice.

emmanuel · **Posted:** Tue Jan 27, 2004 4:38 pm

mkthomas5 wrote:

Also if you try and do everyting dynamic, you never really get to take advantage of the database caching statements.
In Oracle **ALWAYS USE BIND VARIABLES***

Not anymore on 9 serie, AFAIK. They have a transparent binder behind the scene.

steve · **Posted:** Wed Jan 28, 2004 12:16 am

The JDBC "Standard SQL Extensions" (those embedded {fn ...} thingies) also supoort the formatting of dates in a standard way (which the drivers then handle in a db-specific fashion behind the scenes). And they can be used in HQL. That way you don't lose the portability.

And the default date formatting is different on most databases.