between issue on time on 2 dates on same day

I'm running a query that selects data from a table and this table has 2 columns with a begindate and a enddate.
Now I want to do a select that select all rows where a given date must be between the begindate and the enddate, however when the given date is the same as the begindate and the enddate it returns nothing !!
The time of the given date is 12:00, the time of begindate is 10:00 and that of the enddate 23:00. So it should return my rows !!

No way it does !!!



My query is runned from Hibernate but also when I do the same in SQuireL SQL it goes wrong.

It looks something like this:

Calendar base = Calendar.getInstance(Constants.NL_LOCALE);
base.set(2007, Calendar.FEBRUARY, 26, 12, 00);

Session session = HibernateHelper.getSession();

String qString = "SELECT * FROM MyTable WHERE :mydate BETWEEN MyTable.begindate AND MyTable.enddate";

q.setDate("mydate", base);

Also a
:dag <= MyTable.begindate AND :dag >= jaarplanactiviteit.MyTable.enddate
Does not do this right......

What is going on here, anyone a clue ??


Comments

  • Hello,

    When I work with dates with oracle, I always prefer work with truncate dates if the time is not important in the selection.

    So, your select may be like that:
    String qString = "SELECT * FROM MyTable WHERE TRUNC(:mydate) BETWEEN TRUNC(MyTable.begindate) AND TRUNC(MyTable.enddate)";

    This is because we always should take care with dates in oracle. Its hard work correctly if we do not take care with the date format (i.e dd/mm/yyyy, or dd-mm-yy hh24:mi:ss and so on ....) which we use in the database and in the application.

    I hope this could be usefull for you.

    Good Luck!!

    : I'm running a query that selects data from a table and this table has 2 columns with a begindate and a enddate.
    : Now I want to do a select that select all rows where a given date must be between the begindate and the enddate, however when the given date is the same as the begindate and the enddate it returns nothing !!
    : The time of the given date is 12:00, the time of begindate is 10:00 and that of the enddate 23:00. So it should return my rows !!
    :
    : No way it does !!!
    :
    :
    :
    : My query is runned from Hibernate but also when I do the same in SQuireL SQL it goes wrong.
    :
    : It looks something like this:
    :
    : Calendar base = Calendar.getInstance(Constants.NL_LOCALE);
    : base.set(2007, Calendar.FEBRUARY, 26, 12, 00);
    :
    : Session session = HibernateHelper.getSession();
    :
    : String qString = "SELECT * FROM MyTable WHERE :mydate BETWEEN MyTable.begindate AND MyTable.enddate";
    :
    : q.setDate("mydate", base);
    :
    : Also a
    : :dag <= MyTable.begindate AND :dag >= jaarplanactiviteit.MyTable.enddate
    : Does not do this right......
    :
    : What is going on here, anyone a clue ??
    :
    :
    :

  • SELECT *[color=Green][size=2][/size][/color]
    FROM
    table name
    WHERE
    CreatedDate between 'selected date' AND 'selected date' OR date(datefieldname) = 'selected date'
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories