SQL query for date & time interval

[Former Member]

Hello everyone,

there is my problem

I have to make a search for events that are taking place during inserted date & time .

There's the table structure:

event

datefrom

dateto

timefrom

timeto

...

The problem is, when I try:

select * from xyz where datefrom <= inserted_date and dateto >= inserted_date and timefrom <= inserted_time and timeto >= inserted time;

then I don't get back all records.

eg: user inserts date = 10.10.2010 and time 10:00 and in the table is event [abc 11:00 9.10.2010 -> 09:00 11.10.2010] stored following way:

event abdc

datefrom 09.10.2010

dateto 11.10.2010

timefrom 11:00

timeto 09:00

...

=> this event won't be found, because the sql logic compares only single parameters and not their context.

Do you have any idea, how to solve this issue?

Thank you.

Lukas

[Former Member]

Hello everyone,

there is my problem

I have to make a search for events that are taking place during inserted date & time .

There's the table structure:

event

datefrom

dateto

timefrom

timeto

...

The problem is, when I try:

select * from xyz where datefrom <= inserted_date and dateto >= inserted_date and timefrom <= inserted_time and timeto >= inserted time;

then I don't get back all records.

eg: user inserts date = 10.10.2010 and time 10:00 and in the table is event [abc 11:00 9.10.2010 -> 09:00 11.10.2010] stored following way:

event abdc

datefrom 09.10.2010

dateto 11.10.2010

timefrom 11:00

timeto 09:00

...

=> this event won't be found, because the sql logic compares only single parameters and not their context.

Do you have any idea, how to solve this issue?

Thank you.

Lukas

[hubert_heitzer]

Hi Lukas,

how about

select * from xyz where inserted_date between datefrom and dateto and inserted_time between timefrom and timeto;

Regards, Hubert

Edited by: Hubert Heitzer on May 31, 2010 4:06 PM

[Former Member]

Hello Hubert,

thank you very much for your proposal, but your code makes the same select as mine (but in a more user friendly look :).

Table:

datefrom 09.10.2010

dateto 11.10.2010

timefrom 11:00

timeto 09:00

Event date = 10.10.2010 and time 10:00.

Your query:

select * from xyz where 10.10.2010 between 09.10.2010 and 11.10.2010 (that's so far ok) and 10:00 between 11:00 and 9:00 (no return there, because 10:00 doesn't lie within interval 11:00 -> 9:00... actually no value lies there) ;

Don't you have any other idea?

Thank you

Lukas

[ThomasZloch]

Try something like:

( date > datefrom or ( date = datefrom and time >= timefrom ) ) and

( date < dateto or ( date = dateto and time <= timeto ) )

Thomas

[Former Member]

Hi Thomas,

thanks for proposal, it seems that this should work! Stupid me that I haven't got this idea.

Kind Regards

Lukas