InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples
FAQs

Software
Downloads
Documentation
CVS Repositories
Roadmap
License

About Enhydra.org
Who We Are
News, Articles & Events
Getting Involved
Contact Us

Community
Demos
Contributions
Resources
Case Studies
On The Edge! -NEW-
Commercial Vendors

-----Oprindelig meddelelse-----
Fra: owner-instantDB@enhydra.org [mailto:owner-instantDB@enhydra.org]På vegne af Kenneth Ericsson
Sendt: 6. november 2000 15:40
Til: instantdb@enhydra.org
Emne: InstantDB: Problems with, DATE and AND in SQL.

Hello out there.

I have a problem with dates in my database. When I want to compare if a date is inside or outside a given time-period I dont get the expected results. Using "bigger than date1 AND smaller than date2" (for detailed syntax, see example below) I dont get the right result.

The only comparison that works is if the dates compared are checked for equality.

Example:

I have a test table "Period" with 4 rows of data. The rows looks like this:

Period_id    Start_tid           Stopp_tid            Resurs_id    Uppdrag_id
1000           2000-08-01    2000-08-15        1000            1000
1001           2000-08-03    2000-08-14        1001            1001
1002           2000-10-01    2000-10-05        1000            1001
1003           2000-10-06    2000-10-10        1001            1001
 

My Sample script looks like this.

e  DROP TABLE  Period;
e  CREATE TABLE Period
    (Period_id        INT  PRIMARY KEY AUTO INCREMENT,
     Start_tid        DATE,
     Stopp_tid      DATE,
     Resurs_id      INT,
     Uppdrag_id   INT);

The mission is:   I want all "Resurs_id" that have periods overlap my new period "Tillg_f"  -> "Tillg_t"

This is my debug results coming out of my servlet application.
 
 

Debug info for com.gefionsoftware.instantonline.basic.DBCollectServlet

     Database call info
       SQL statement: SELECT DISTINCT Resurs_id FROM Period WHERE Start_tid > {d '2000-07-07'} AND {d '2000-07-10'} > Stopp_tid
       Number of rows read/modified: 2

     Request parameters
       result: Resurs_id.Lista_pa
       columns: DISTINCT Resurs_id
       debug: true
       condition: Start_tid > $session|Resurs_sok.prio_1_pa(Tillg_f)||date$ AND $session|Resurs_sok.prio_1_pa(Tillg_t)||date$ > Stopp_tid
       dbname: db_pa
       table: Period
       runif: $session|Resurs_sok.prio_1_pa(Kat)$ = 2

As you can see the database found 2 "Resurs_id" there it shuld be none. It seems like the expr. with " > " doesn't work. When I test with " = " it seems to work and when I test the expr. one and one it also works.  My version of instantdb is 2.41..

Can anyone help me?

Regards!!
 
 

// Kenneth Ericsson
MindProxy AB  Sweden.